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.
lbYaw0.jpeg
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
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.

Answered 12/05/2014 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share