/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Ticketing Queue Custom E-mail

03/25/2019 540 views

When we went onto KACE SMA 9.0, we had to set up custom ticketing rules in order to notify on a new ticket (the CC rules stopped applying to new tickets for some reason). Now, I found sql code online for setting this up if you want the same users notified of any ticket, regardless of the category. However, I am hoping to find a way to set up a custom rule that fires off an e-mail to a certain group of people only if a certain category is selected. Here is the sql code I have now, and I tried putting in CAT.NAME LIKE '% %' down at the WHERE line, 4 up from the bottom, and that did not work. Any thoughts?

SELECT

      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://********/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

   '****@****.****' 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 '%Created%'

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

    GROUP BY HD_TICKET.ID DESC

    HAVING 1=1

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

Modify this section:

WHERE
     C.DESCRIPTION LIKE '%Created%'

to this:

WHERE
     C.DESCRIPTION LIKE '%Created%'
and CAT.NAME = "Category name"



Answered 03/26/2019 by: chucksteel
Red Belt

  • Thanks once again @chucksteel! That worked perfectly.

    One more question I have; when a user submits a ticket via email, they of course do not have the ability to specify a category. Is there a way to cause a new ticket e-mail to trigger to certain people based on certain key words in the e-mail subject line or body?
    • Sure. Instead of checking C.DESCRIPTION use HD_TICKET.TITLE or HD_TICKET.SUMMARY. For instance:
      HD_TICKET.TITLE like "%monkeys%"
      • LOL at the "monkeys". Thanks again sir.

All Answers

0

What can I change on this SQL Code to where when I add comment it will automatically go to "'****@****.****' AS NEWTICKETEMAIL -- $newticketemail"

Answered 04/01/2019 by: SoheebH
White Belt

  • Do you want a query specifically for adding a comment or any change? For any change you can remove the C.DESCRIPTION LIKE '%Created%' so the where statement looks like this:
    WHERE CAT.NAME = "Category Name"
    • Just need a query specifically for adding a comment to the same distribution list that is in the CC list and also '****@****.****' AS NEWTICKETEMAIL -- $newticketemail

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