/build/static/layout/Breadcrumb_cap_w.png
03/25/2019 231 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://kace-1000a.directions.local/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