/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
0

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 5 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 5 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 5 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 5 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

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