K1000 SQL Query for the Ticket Modified Notification
Hello, I'm wondering if someone can give me the exact SQL query used for the "Ticket Modified" Email on Events? I would like to know where the $change_desc is being pulled from. The reason for this is I want to create my own customized Ticket Modified rule whenever there's a change in a ticket, where I can add more information, primarily the custom field values in the email portion.
I'd appreciate the help. I've attached the screenshot of the Ticket Modified section of the Service Desk Email Notifications to give you guys a better idea of what I'm talking about.
I'd appreciate the help. I've attached the screenshot of the Ticket Modified section of the Service Desk Email Notifications to give you guys a better idea of what I'm talking about.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
9 years ago
Changes to tickets are stored in HD_TICKET_CHANGE and HD_TICKET_CHANGE_FIELD. You can join to them by using the <CHANGE_ID> variable that KACE will replace at runtime:
JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID
and TICKETCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD CUSTOMFIELDCHANGE ON CUSTOMFIELDCHANGE.HD_TICKET_CHANGE_ID=<CHANGE_ID> and CUSTOMFIELDCHANGE.FIELD_CHANGED = "CUSTOM_FIELD_VALUE1"
The second statement would contain changes to the custom value 2 (the columns in the database are 0 based so you need to subtract 1 from the field number).
The change description is then found in TICKETCHANGE.DESCRIPTION. The new value for the custom field is CUSTOMFIELDCHANGE.AFTER_VALUE.
