Hello,

I have a question regarding the ticket escalation system. I want to make a report that mails every admin users (theres 4 of them including me) if a ticket has the status ''New'' for the past 5 days based on the creation date.

Example,
I create a ticket on the 3th of December. If the ticket has the status ''New'' till 8th of December, I want it to escalate and send a report to the admins.
If a ticket get created on the 4th, i want it to escalate on the 9th, and so on. 

I can't find any SQL code on the web to work with and with the wizard is in my opinion not working the way i want.
So I hope anyone can point me in the right direction or even share there SQL, so I can work from that. :)

Thanks in advance,

Ekkerd.
1 Comment   [ + ] Show Comment

Comments

  • When you say you want the ticket to escalate do you want to change the owner? If you just want a report of tickets with this condition then using a scheduled report would work also.
Please log in to comment

Community Chosen Answer

2
This is the ticket rule I use for that purpose. If a ticket is ignored (no owner and status new) for more than an hour then it emails the team if it's during business hours. You can change the times around to suit your needs. Make sure you put an upper limit or else it'll keep emailing the address on the selected time schedule. (If it checks every hour it'll email every hour once the conditions are met)

Select 'TEAM EMAIL ADDRESS HERE' as SUPPORT, T.ID as ID, T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID) 
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)  created over an hour ago
AND T.CREATED > SUBDATE(NOW(), INTERVAL 2 HOUR)   created less than 2 hours ago
AND T.OWNER_ID = 0    no owner assigned
AND T.HD_QUEUE_ID = 1

below code is to set during business hours 
and HOUR(NOW())>=6
and CURTIME()<='18:00:00' 
and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') 
and DAYOFYEAR(NOW()) NOT IN (1,360)   /* not xmas or new year's */
ORDER BY T.ID ASC


Column containing email addresses = SUPPORT
Answered 12/03/2014 by: h2opolo25
Red Belt

  • Hi, I am running into an error when running this script and I'm not sure why. Will this work on 6.2x? Thanks
Please log in to comment

Answers

1
Thanks for answering! @h2opolo25, your SQL code works really good! Thanks for sharing it with me! :)

Sincerly,
Ekkerd
Answered 12/08/2014 by: Ekkerd
Senior White Belt

Please log in to comment
Answer this question or Comment on this question for clarity