I have a simple rule that I created to email me when a ticket goes from unassigned to one of our techs:

 

SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%M %d, %Y') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.CREATED,'%I:%i:%s %p') AS TCREATED, -- $tcreated
      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://kbox/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.CUSTOM_3 AS OWNER_DNAME, -- $owner_dname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.FULL_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.CUSTOM_3 AS SUBMITTER_DNAME, -- $submitter_dname
      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
      'xxxxx@xxxx.com' AS EMAIL -- $email
    
    
    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 'Changed ticket Owner from "Unassigned" to%'
     
    
      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1
 As you can see it uses the description to see what was changed.
 It works great if I just go in and change just the owner.
My problem is if for instance I change multiple values in the
ticket such as category and owner,and then hit save, the
the rule will not work. It adds those multiple descriptions to one
description with multiple bulets.Does anyone know how to check the
second bullet point of a description? or another way I could write this?



Answer Summary:
The second solution is to detect a change in the owner field. To do this you need to add another join: JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID= AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED = "OWNER_ID" Now in your where clause you would have WHERE HD_TICKET_CHANGE_FIELD.BEFORE_VALUE = 0
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

There are two solutions:

Change your C.DESCRIPTION query to

C.DESCRIPTION LIKE '%Changed ticket Owner from "Unassigned" to %'

This will match whether or not the note for changing the owner is first in the description or not. 

The second solution is to detect a change in the owner field. To do this you need to add another join:

JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID> AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED = "OWNER_ID"

Now in your where clause you would have

WHERE HD_TICKET_CHANGE_FIELD.BEFORE_VALUE = 0

 

Answered 01/28/2014 by: chucksteel
Red Belt

  • Thank you so much chuck. You have really helped me get a better grasp of what is going on behind the scenes with these rules. If you don't mind me asking how do you go about creating/testing the rules that you made. Do you just write them and start creating test tickets? Do you use mySQL? Also, how did you learn all of this stuff, just experience? Is there a good SQL book out there that you recommend?
    • I have a queue setup for testing rules and I create them there first. This way I can create tickets without them polluting our production queues and I don't have to worry about them sending emails or making changes to real tickets by accident.

      I use MySQL Workbench quite a bit. The only problem with using it for working on rules is you need to remember to hardcode ticket IDs and Change IDs since KACE normally uses variables for those.

      I've been using MySQL with programming projects for a while so I wasn't new to it when we moved to KACE. There are great books for SQL out there but all of my knowledge has been on the job training. The MySQL manuals and stackoverflow.com have been my best resources (outside of this community for KACE specific knowledge).
Please log in to comment
Answer this question or Comment on this question for clarity

Share