/build/static/layout/Breadcrumb_cap_w.png

How can I require a field based on ticket age?

I want to require the Ticket Owner to popluate a new custom text field when a ticket is being closed and is over 2 days old. The purpose is for the technicians to explain why the ticket was not closed in less than 2 days and to provide an easy way to report on these tickets to management. My problem with on-close ticket rules in the past has been that the notice is still sent to the end user, it would be nice to preempt the on-close message as well.

As always, any help is appreciated.

BTW: The old interface was much more firndly for searching Ticket Rules. Any tips?


0 Comments   [ + ] Show comments

Answers (4)

Answer Summary:
You can use the basic Rule builder, and then replace the final line of logic with the conditions that work "and (((HD_TICKET.CREATED < DATE_SUB(NOW(),INTERVAL 2 DAY)) AND HD_STATUS.NAME not like '%closed%') and HD_TICKET.HD_QUEUE_ID = 1 )"
Posted by: chucksteel 11 years ago
Red Belt
1

For some reason my entire comment didn't get added. I'm posting it here:

The query is as follows:

 

select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,

                        HD_STATUS.NAME AS STATUS_NAME,

                        HD_STATUS.ORDINAL as STATUS_ORDINAL,

                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,

                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,

                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,

                        STATE, 

                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,

                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED, 

                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED, 

                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED, 

                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,

                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED, 

                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,

                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,

                        case upper(STATE) 

                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) 

                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) 

                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,

                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,

                        U1.FULL_NAME as OWNER_FULLNAME,

                        U1.EMAIL as OWNER_EMAIL,

                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,

                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,

                        U2.FULL_NAME as SUBMITTER_FULLNAME,

                        U2.EMAIL as SUBMITTER_EMAIL,

                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,

                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,

                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,

                        Q.NAME as QUEUE_NAME                        

                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)

                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID

                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID

                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID

                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID

                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

                        where HD_PRIORITY.ID = HD_PRIORITY_ID

                        and HD_STATUS.ID = HD_STATUS_ID

                        and HD_IMPACT.ID = HD_IMPACT_ID

                        and HD_CATEGORY.ID = HD_CATEGORY_ID

                        and ( HD_TICKET.HD_QUEUE_ID = 2 AND state='opened' and HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 2 DAY))

 

Note that this has a HD_QUEUE_ID = 2, you'll need to update that value to reflect the appropriate queue ID for your queue.

 

 


Comments:
  • Been busy, sorry it took so long.
    This got it, kinda. I created a new rule, stole your date logic but had to reverse the "> DATE" to "< DATE" and changed "state = 'opened'" to "HD_STATUS.NAME not like '%closed%'" and got it to work.

    Thanks so much for your help. - Transam 11 years ago
Posted by: chucksteel 11 years ago
Red Belt
1

I think that the one way you could accomplish this would be to have a rule that checked to see if the field was empty on a closed ticket that had been opened for two days. You can't stop the technician from closing the ticket, but you can send them a reminder that the field needs to be filled out every fifteen minutes that they don't do so. 

 

The problem with configuring the field itself to be required on a closed ticket is that it would be required for all tickets, not just those that were open for more than two days. Here's another solution:

  • Configure the service desk to always require the field to have a value in order to be closed.
  • Configure a default value for the field so that the ticket can be closed for now.
  • Create a rule that deletes the default value once the ticket has been open for two days. Once this rule fires it should prevent the technician from closing the ticket until they enter a value.

I'm not positive if that solution would work, but I think it should.

As per your other question concerning finding ticket rules, I would suggest that if you (and others) add the appropriate tags to your questions they would be categorized in a way that would make them easier to find. For instance this question should probably have the "Dell KACE K1000" and perhaps a "Service Desk Rules" tags applied.


Comments:
  • Great idea man! I think that would work. - dchristian 11 years ago
Posted by: Aaron Cool 11 years ago
Red Belt
0

Edit Ticket Layout\Custom_1 in Ticket Queue>Customize these Values with Required on Close & Owners Only-Hidden from users. 

In Custom Fields> specify 'Field Type' - as Single Select, 'Select Values' with a couple of values like Customer MIA, Customer Didn't Reply, followed by comma-separated strings & 'Default' - Specify a text value. 

If you don't want the End-user to get an email, deselect 'New Ticket Via Email' at Service Desk Configuration - [Customize Emails]

You can find Ticket Rules at the bottom of the page after loading 'Service Desk Queues' \ Custom Ticket Rules: [Customize]

If select properly, this new Ticket Rules will apply and it may require the Technicians to fill in the created Custom_1 Fields. 

What do you want the Ticket Rules to do for you after 2days? 

Posted by: Transam 11 years ago
Orange Belt
0

chucksteel  - require custom field to close, enter a default value, clear value after 2 days...I like it! Even sounds like something I can do with a Basic Rule. Any hints on what syntax to put in the Rule? Figure Time_Opened < ??? (today - 2days).


Comments:
  • The rules wizard doesn't form date comparisons correctly, unfortunately. Here's a query that should find tickets that have a state of open and were created more than two days ago:

    select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
    STATE,
    if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
    if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
    if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
    if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
    if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
    if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
    if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
    if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
    case upper(STATE)
    when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
    when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
    else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
    if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
    U1.FULL_NAME as OWNER_FULLNAME,
    U1.EMAIL as OWNER_EMAIL,
    if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
    if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    U2.EMAIL as SUBMITTER_EMAIL,
    if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
    if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
    if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
    Q.NAME as QUEUE_NAME
    from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
    LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
    LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_CATEGORY.ID = HD_CATEGORY_ID
    and ( HD_TICKET.HD_QUEUE_ID = 2 AND state='opened' and HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 2 DAY))

    Note that my HD_QUEUE_ID is 2, you need to change that value to the appropriate ID of your queue. - chucksteel 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Link

Related Links

Post

Related Posts

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ