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
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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!
Please log in to comment

Answer Chosen by the Author

0
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
Answered 01/27/2016 by: RSanders
White Belt

Please log in to comment

Answers

0
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.
Answered 01/26/2016 by: Hobbsy
Red Belt

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

Share