Custom Email Notifications for Updates
We use custom ticket rules for almost all of our email notifications as the built-in variables leave much to be desired. The only built-in one we still use is the update notification. Our users dislike the information that is displayed for $change_desc because the comment gets lost in all the other info (like when we change the status, category, priority, owner, etc...).
I'd like to do a custom ticket rule for update notifications that displays the last comment made on a ticket by itself. Does anyone know how to query that? I'm fine with writing my own select statements to pull the info I need, but I'm not exactly sure where that info resides or how to tell it to only pull that last comment.
Please log in to answer
Posted by: chucksteel 6 years ago
KACE uses the <CHANGE_ID> variable (substituted at runtime) for the latest change on a ticket. Using that you can get the most recent entry in the HD_TICKET_CHANGE table with this join:
JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID
The comment is stored in HD_TICKET_CHANGE.COMMENT.