/build/static/layout/Breadcrumb_cap_w.png

Custom Ticket Rule to Notify Team of Any Ticket Change

I am trying to write a custom inventory rule (CTR) that notifies our IT Team of any change on a ticket when for our Change Management Queue.  However, we have a custom field value labeled Team Notify that is checked to send an email and unchecked to prevent an email from going out.  This is desired so that small changes can be made on the Request for Change (RFC) without spamming our whole team.


My issue is that I am not getting the results I expect when running the rule.  Should be getting several different changes (see below):

x+TDkXFL67VawAAAABJRU5ErkJggg==

But instead, I only get a few and they are not the latest change:

W8qM28Omx5AAAAAElFTkSuQmCC

Here is my code:

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

 SELECT


      -- ticket fields


      HD_TICKET.ID, -- $id


      HD_TICKET.ID AS TICKNUM, -- $ticknum


      HD_TICKET.TITLE, -- $title


      HD_TICKET.CUSTOM_FIELD_VALUE0 AS AFFECTED_SYSTEMS, -- $affected_systems


      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE1,'%b %d %Y') AS EFFECTIVE_DATE, -- $effective_date


      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE2,'%b %d %Y') AS FOLLOW_UP, -- $follow_up


      HD_TICKET.CUSTOM_FIELD_VALUE3 AS CHANGE_REASON, -- $change_reason


      IF(HD_TICKET.CUSTOM_FIELD_VALUE6=1, "Yes", "No") AS TEAM_NOTIFY, -- $team_notify


      HD_TICKET.HTML_SUMMARY, -- $html_summary


      HD_TICKET.CC_LIST AS CC_LIST, -- $cc_list


      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

      

      C.TIMESTAMP, -- $timestamp


      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',


       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://usckace1000/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


      IFNULL(OWNER.FULL_NAME, 'Unassigned') 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


      SUBMITTER.MOBILE_PHONE AS SUBMITTER_MOBILE, -- $submitter_mobile


      SUBMITTER.WORK_PHONE AS SUBMITTER_WORK, -- $submitter_work


      -- 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


      'IT-ChangeAlerts@Domain.com' AS NEWTICKETEMAIL -- $newticketemail


    FROM HD_TICKET


     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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 NOT LIKE '%TICKET CREATED%'

     AND S.NAME != 'Completed'

     AND S.NAME != 'Cancelled'

     AND S.NAME != 'Needs Action'

     AND HD_TICKET.CUSTOM_FIELD_VALUE6=1


      /* this is necessary when using group by functions */


    GROUP BY HD_TICKET.ID


    HAVING 1=1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any ideas?


Thanks - Cloud


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: cloudcomp 1 year ago
White Belt
1

Top Answer

I resolved the issue with the code below.  Specifically, I was getting results for every ticket that was in the queue where Team_Notify was checked.  The main change I made was in one of the join statements where I added a Kace specific variable of AND C.ID=<CHANGE_ID>.  See below.

------------------------------------------------------------------

 SELECT


      -- ticket fields

      HD_TICKET.ID, -- $id

      HD_TICKET.ID AS TICKNUM, -- $ticknum

      HD_TICKET.TITLE, -- $title

      HD_TICKET.CUSTOM_FIELD_VALUE0 AS AFFECTED_SYSTEMS, -- $affected_systems

      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE1,'%b %d %Y') AS EFFECTIVE_DATE, -- $effective_date

      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE2,'%b %d %Y') AS FOLLOW_UP, -- $follow_up

      HD_TICKET.CUSTOM_FIELD_VALUE3 AS CHANGE_REASON, -- $change_reason

      IF(HD_TICKET.CUSTOM_FIELD_VALUE6=1, "Yes", "No") AS TEAM_NOTIFY, -- $team_notify

      HD_TICKET.HTML_SUMMARY, -- $html_summary

      HD_TICKET.CC_LIST AS CC_LIST, -- $cc_list

      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

      C.TIMESTAMP, -- $timestamp

      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://usckace1000/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

      IFNULL(OWNER.FULL_NAME, 'Unassigned') 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

      SUBMITTER.MOBILE_PHONE AS SUBMITTER_MOBILE, -- $submitter_mobile

      SUBMITTER.WORK_PHONE AS SUBMITTER_WORK, -- $submitter_work


      -- 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

      'IT-ChangeAlerts@Domain.com' 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 NOT LIKE '%TICKET CREATED%'

     AND S.NAME != 'Completed'

     AND S.NAME != 'Cancelled'

     AND S.NAME != 'Needs Action'

     AND HD_TICKET.CUSTOM_FIELD_VALUE6=1 /* TEAM_NOTIFY is "Yes" */

     

/* this is necessary when using group by functions */

    GROUP BY HD_TICKET.ID

    HAVING 1=1

------------------------------------------------------------------

Posted by: Hobbsy 1 year ago
Red Belt
0

I think you may be overthinking this....if you want an email to be sent when the box is ticked, then you only need to run the rule on ticket save when the custom field you use for the tick box is = 1

If you set up a single address mailing list for the team, you can then use the ticket rule to send the email and also reset the tick box back to a zero value.

Net result should be if the box is ticked an email is sent.

You will just need to find the $variable to add to the ticket rule email that contains the last update


Comments:
  • Hobbsy, thanks for taking a look at this. I believe I may have missed clarifying my issue. I am trying to get the Description field for the latest change in the HD_TICKET_CHANGE table. I get results but they are not the latest change. The rule runs fine for the checkbox to send the email. However, the information inaccurate. - cloudcomp 1 year ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ