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?
Answers (4)
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 )"
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 12 years ago
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 12 years ago
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?
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 12 years ago