Ticket Rule doesnt work when two fields are changed at once.
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?
0 Comments
[ + ] Show comments
Answers (1)
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
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
Please log in to answer
Posted by:
chucksteel
10 years ago
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
Comments:
-
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? - Kevino2010 10 years ago
-
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). - chucksteel 10 years ago
