Ticket escalation
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
-
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. - chucksteel 9 years ago
Answers (2)
Please log in to answer
Posted by:
h2opolo25
9 years ago
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
Comments:
-
Hi, I am running into an error when running this script and I'm not sure why. Will this work on 6.2x? Thanks - psullivanassoc 9 years ago
Posted by:
Ekkerd
9 years ago
Thanks for answering! @h2opolo25, your SQL code works really good! Thanks for sharing it with me! :)
Sincerly,
Ekkerd