/build/static/layout/Breadcrumb_cap_w.png

Custom ticket rule for notification on queue change

I have found a custom ticket rule on the forums that should accomplish what I want however, I know zero SQL, and when I try to use the "view ticket search results" I get a SQL syntax error but I"m not sure where the syntax error is. I have the SQL statements below as well as an image of the config for the rule. Any help would greatly be appreciated.

I have the following sql in the "Select SQL:" Box

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://SMA/adminui/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

      'distribution@email.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 like concat("%Changed ticket Queue from%to%.%")

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

    GROUP BY HD_TICKET.ID

    HAVING 1=1



D8EZHSS5ZwrEAAAAAElFTkSuQmCC

Edited: Removed the Domainname and replaced with SMA /NK


1 Comment   [ + ] Show comment
  • dreame95: are you still around? I need a favor.. if you can please respond. thank you. - barchetta 4 months ago

Answers (1)

Posted by: barchetta 7 months ago
2nd Degree Black Belt
1

View ticket rule results wont work for most rules. It would have to be essentially a report.  So just ignore it, not sure why kace has it there.  


If you dont have a development server (which you should) then put it in a test queue and test it.  


Comments:
  • Thanks, I used the rule in a test queue, however the last run log shows 0 selected rows. - dreame95 7 months ago
    • How did you run your test? In order for it to run and send an email it would have had to be 1.) set to run on ticket save and 2.) a ticket would have needed to be MOVED from another queue to the queue you have the rule enabled in. In your screenshot, the rule is not enabled. I would for now, also set append comment to ticket option so you know if the rule ran on a ticket.. then confirm email delivery of course. - barchetta 7 months ago
    • This actually looks like a rule I contributed to this forum. :) - barchetta 7 months ago
      • It might be. I currently have the rule enabled, and set to "on ticket save". I tried appending a comment to ticket when the rule runs and I don't see the comment either. - dreame95 7 months 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