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.
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,
Community Chosen Answer
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 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
Please log in to comment
log in to commentPlease