Service Desk rule for date and time escalation(s)
I want to create a rule that says if a ticked based on (create time) is x days old then then escalate to x and change status to Y.
I can do the 2nd part easily in the wizard. How do I get the time date thing to work with the wizard? If I cant, any recommendations.
Thanks in Advance.
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
h2opolo25
9 years ago
Here's what I did to make this happen....
Created a Timestamp Custom Field in the queue config and left it as hidden. Called it Escalation Time (DO NOT DELETE)
Created 4 rules....
1. When a new ticket is created, email the IT team and set the custom escalation field I created to the created time of the ticket.
2. If priority is Low and Escalated field is over 5 days then send an email to specific people and move priority to Medium and set the Escalated field to the current time.
3. If priority is Medium and Escalated field is over 5 days then send an email to specific people and move priority to High and set the Escalated field to the current time.
4. If priority is High and Escalated field is over 1 day then send an email to specific people and move priority to Critical and set the Escalated field to the current time.
I turned off escalation in the queue customization screen for everything but Critical. Critical I set at 4 hours.
I'll post the rules in comments to follow....
Comments:
-
-- Email the email in the NEWTICKETEMAIL line when a new ticket is created
-- Update a custom field set to timestamp that keeps track of escalation. All escalation rules use this rule.
-- SELECT SQL:
SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kacewebsite/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
'email@email.com' AS NEWTICKETEMAIL -- CHANGE THIS EMAIL FOR EACH QUEUE
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE 'TICKET CREATED%'
GROUP BY HD_TICKET.ID
HAVING 1=1
-- UPDATE SQL:
update HD_TICKET T
set T.CUSTOM_FIELD_VALUE3 = T.CREATED -- custom field set to timestamp that is used for escalation purposes
where T.HD_QUEUE_ID = 1 -- select desired queue
and T.ID in (<TICKET_IDS>)
/*
OPTION: Email each recipient in query results
Subject: [TICK:$ticknum] NEW TICKET: $title
Column contaning email addresses: NEWTICKETEMAIL
Message:
$submitter_fname has opened a ticket.
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment
*/
-- CUSTOM FIELD NUMBERS IN THE GUI ARE ONE NUMBER HIGHER THAN IN MySQL
-- ex: CUSTOM_FIELD_4 in the GUI is CUSTOM_FIELD_VALUE3 in MySQL - h2opolo25 9 years ago -
-- rule escalates low priority tickets to medium after 5 days. Custom field created as timestamp to track escalation.
-- SELECT SQL:
select T.ID, T.TITLE, O.FULL_NAME as owner, SU.FULL_NAME as submitter, 'email@email.com' as SUPPORT from HD_TICKET T -- EMAILS UPDATE TO THESE EMAILS
left join HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
left join HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
left join HD_STATUS S on S.ID = T.HD_STATUS_ID
left join USER O on O.ID = T.OWNER_ID
left join USER SU on SU.ID = T.SUBMITTER_ID
where P.NAME = 'Low'
and S.State = 'Opened'
and Q.NAME = 'TROUBLE TICKETS' -- change to desired queue
and T.CUSTOM_FIELD_VALUE3 < subdate(now(), interval 5 day) -- change to desired time frame
-- UPDATE SQL:
update HD_TICKET T
set T.HD_PRIORITY_ID = 1, -- change priority ID according to HD_PRIORITY table
T.CUSTOM_FIELD_VALUE3 = Now()
where T.HD_QUEUE_ID = 1 -- change to desired queue
and (T.ID in (<TICKET_IDS>))
/*
OPTION: Append comment to ticket
Comment: Ticket Escalated from Low to Medium
Owners Only selected
OPTION: Email each recipient in query results
Subject: [TICK: $id] TICKET ESCALATION
Column containing email addresses: SUPPORT
Message:
The ticket with:
Ticket ID: $id
Title: $title
Owner: $owner
Submitter: $submitter
has been escalated!
*/
-- CUSTOM FIELD NUMBERS IN THE GUI ARE ONE NUMBER HIGHER THAN IN MySQL
-- ex: CUSTOM_FIELD_4 in the GUI is CUSTOM_FIELD_VALUE3 in MySQL - h2opolo25 9 years ago
Posted by:
chucksteel
9 years ago
The date/time functions don't usually work correctly when you use the wizard so I normally have to edit the MySQL query manually to make them work. Do you have experience with MySQL?
Posted by:
Paloma
9 years ago
Here is my ticket rule.
it sends and email to SUPPORT every 1:30h if nobody has done nothing on the ticket. there are two things that i would like to improve but i dont know how (if sb would help....it would be awesome)
-it sends an email EVERY 1:30h if it still doesnt suffer any change. i would like that it sends just one email after the first 1:30h without changes
-i would like that it works just in working hours from 8:00 to 18:00
->SELECT SQL
select 'somebody@yourcompany.br' as SUPPORT, HD_TICKET.TITLE AS TITLE,HD_TICKET.ID AS ID,
HD_STATUS.NAME AS STATUS,Q.NAME as QUEUE,
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,
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.MODIFIED) end as AGE,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL
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
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.CREATED = HD_TICKET.MODIFIED
and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 )
GROUP BY HD_TICKET.TITLE, HD_TICKET.ID
->active the tickbox for sending and email for every destinary of the consult
-Tittle:Escalating
-Email:SUPPORT (here is where you choose the person or people who is gonna receive the alert. you have to write it in the select query)
-Message:
Tickets não atribuidos em 1:30h.
Creado: $sort_time_created
Titulo: $title
Ticket ID: $id
Solicitante: $submitter_fullname
Corpo do chamado: http://youraddressforservicedesk/adminui/ticket.php?ID=$id
Comments:
-
for the timetable GillySpy has solved it here: http://www.itninja.com/question/use-escalation-timer-with-a-different-field-sla-counter - Paloma 9 years ago
-
Here it is the full rule with the problems solved.
http://www.itninja.com/question/k1000-rules-just-one-notification-on-ticket-rule - Paloma 9 years ago
