Escalation Email Alerts for Unassigned Tickets?
Ok guys,
We would like to have an email generated for escalation alerts when the owner is Unassigned. Is this possible? It appears that the way this is configured by default is that escalation emails only get sent to owners (unless you select the check boxes for submiter, approver, etc) but not for Unassigned tickets. How can I also have the system send an email in this situation? We sometimes have Critical Priority tickets that get submitted over the weekend and we get no notifications of this because email notifications dont seem to be configurable for an Unassigned owner.
Thanks in advance!
Rick
1 Comment
[ + ] Show comment
-
Thanks for responding Hobbsy. However, I am not very familiar with the SQL language. Could you provide me with an example perhaps? I understand the Kace system and how to create custom rules but I am not sure how to write/modify the code per your suggestion. After I read this I created a custom rule (made sure to select owner full name = 0 in the wizard) and when it displayed the SQL code I saw no code in regard to HD_TICKET.OWNER_ID=0 as you mentioned. Thanks again! - RSanders 8 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
8 years ago
You can indeed set up an escalation, a few things to think about would be when do you want the escalation to fire and who are you going to escalate to?
You can then build a rule that says if the field HD_TICKET.OWNER_ID = 0 and the escalation has been reached fire the ticket rule.
A couple of useful tips for this, as you may want the ticket rule to check every 15 minutes, I assume you only want the rule to send a single email?
If this is the case, create a custom field, a single select with a default value of '1' and when only allow the rule to fire when the value of this field is '1', setting the value to '2' the first time the rule is run, whereby preventing multiple emails. When the functionality is working simply hide the field and the rule will continue to work in the background.
Also hint number two, if you want to escalate to a Supervisor email address, populate a custom field with the email address of the supervisor and then hide it. Then within the select section of the ticket rule, select the hidden field. You can then call the email address from within the email portion of the ticket rule.
Comments:
-
I am trying to do the same thing except, if a ticket that is set to Critical Priority and the owner is unassigned, then we would like to receive an email every 5 minutes if the ticket has not been claimed by an owner. Can you give me a step-by-step guide on this? - abratton 6 years ago
-
Firstly you will not be able to fir the rule every 5 minutes as 15 minutes is the minimum recurring schedule for a ticket rule that you can set. Secondly who is going to be the recipient of the email alert? You should be able to build a rule that says if the priority is critical and the owner is unassigned, just send an email, no update required. Probably best to ask a fresh question to get a solution ;o) - Hobbsy 6 years ago
Posted by:
RSanders
8 years ago
Top Answer
I finally figured this out with some help from one of our DBA's. You have to pay close attention to the variables in the code and make sure to identify the queue ID as well. NOTE: THIS DOES NOT WORK IF YOU ENTER A DISTRIBUTION GROUP AS THE EMAIL RECIPIENT! Here is the code:
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 https://YOUR.COMPANY.HERE/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
-- other fields
-- -- example of static distribution list
'YOUR EMAIL HERE' AS NEWTICKETEMAIL -- $newticketemail
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%'
and P.ID in ('65', '94', '104', '113', '127') and OWNER.USER_NAME is null and S.ID in ('124', '174', '210', '222', '225', '228', '236')
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Email Config Portion:
Subject
[TICK:$ticknum] NEW TICKET: $title
Column Containing Email addresses
NEWTICKETEMAIL
Message
$submitter_fname has opened a Critical Ticket.
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment