Send and email when Priority changes
Intending to use Custom Ticket Rules I have created a query that should select only tickets that selects tickets who's priority has been deescalated. Then intending to send an email, and add comment to the ticket to prevent duplicate emails. My query works in Toad for Mysql but i receive a generic syntax error when adding to Kace.
Below is the query i'm using. Any help is appreciated.
select distinct(T1.HD_TICKET_ID) AS ID
,SUBSTR(T2.LOCALIZED_DESCRIPTION,INSTR(T2.LOCALIZED_DESCRIPTION,'\\')+3,1) ORG_PRIORITY
,SUBSTR(T2.LOCALIZED_DESCRIPTION,LOCATE('\\',T2.LOCALIZED_DESCRIPTION,INSTR(T2.LOCALIZED_DESCRIPTION,'\\')+6)+3,1) NEW_PROPRITY
FROM HD_TICKET_CHANGE T1
LEFT JOIN HD_TICKET_CHANGE T2 ON T1.ID
WHERE SUBSTR(T2.LOCALIZED_DESCRIPTION,INSTR(T2.LOCALIZED_DESCRIPTION,'\\')+3,1) < SUBSTR(T2.LOCALIZED_DESCRIPTION,LOCATE('\\',T2.LOCALIZED_DESCRIPTION,INSTR(T2.LOCALIZED_DESCRIPTION,'\\')+6)+3,1)
AND T1.LOCALIZED_DESCRIPTION LIKE ('%Priority from%')
AND T1.HD_TICKET_ID > '3540'
AND T2.COMMENT <> 'Notification of priority deescalation has been sent.'
AND T2.HD_TICKET_ID > '3540'
AND T1.ID=T2.ID
Answers (1)
Top Answer
This statement will match tickets on save when the priority has changed. Rather than parsing the ticket change descriptions, use the HD_TICKET_CHANGE_FIELD table. You won't be able to test this rule in Toad because it relies on the variable <CHANGE_ID> that is generated when the ticket is saved.
SELECT ID, CF.BEFORE_VALUE, CF.AFTER_VALUE
-- add other fields needed for email
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE C on C.ID = <CHANGE_ID>
-- <CHANGE_ID> is a variable generated at run time when the ticket is saved
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND FIELD_CHANGED = "PRIORITY_NAME"
-- only get priority name changes
WHERE C.DESCRIPTION not like '%Created%'
-- prevents rule running on new tickets.AND LEFT(BEFORE_VALUE, 1) < LEFT(AFTER_VALUE, 1)
Comments:
-
That's fine with an exception; that does not distinguish between increasing priority or decreasing priority, just a change in priority. - Resef 6 years ago
-
Editing lost a carriage return. This part:
AND LEFT(BEFORE_VALUE, 1) < LEFT(AFTER_VALUE, 1)
should be on its own line. And I guess it should be:
AND LEFT(BEFORE_VALUE, 1) > LEFT(AFTER_VALUE, 1)
Based on the query you posted, I'm assuming that the first character in the priority's name is a number which reflects the level of the priority, higher numbers being more urgent. - chucksteel 6 years ago-
Ahh, Excellent! Thank you i shall test this as soon as i am able. far more simple than i was seeing it. - Resef 6 years ago
-
Thank you i was finally able to test this and it works. There were some minor modifications that needed to be made, and some i wanted to make. I have included my function result in case this is desired in the future.
SELECT HD_TICKET.ID AS ID, CF.BEFORE_VALUE, CF.AFTER_VALUE
-- add other fields needed for email
FROM ORG1.HD_TICKET
JOIN HD_TICKET_CHANGE C on C.ID = <CHANGE_ID>
-- <CHANGE_ID> is a variable generated at run time when the ticket is saved
JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID AND FIELD_CHANGED = "HD_PRIORITY_ID"
-- only get priority name changes
WHERE C.DESCRIPTION like 'Changed%Priority%'
AND C.ID = CF.HD_TICKET_CHANGE_ID
AND C.HD_TICKET_ID = HD_TICKET.ID
-- prevents rule running on new tickets.
-- should be on its own line. And I guess it should be:
AND CF.BEFORE_VALUE < CF.AFTER_VALUE - Resef 6 years ago
