SQL Script to Send an Email to Previous Ticket Owner.
I know nothing about SQL, but have been tasked with creating a SQL Script for Kace 6.3 K1000, that will send an email to the previous ticket owner once the ticket owner changes.
Any help would be massively appreciated.
Answer Chosen by the Author
This should do it:
- Create a New (SQL) ticket rule
- Give it a name and optional Description
- Select SQL:
HD_TICKET.ID as ticket_number, # $ticket_number
HD_TICKET.TITLE as ticket_title, # $ticket_title
ifnull(if(NEWOWNER.FULL_NAME = "", NEWOWNER.USER_NAME, NEWOWNER.FULL_NAME), "Unassigned") as new_owner_name, # $new_owner_name
PREVIOUSOWNER.EMAIL as EMAILCOLUMN
join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
and C.ID = <CHANGE_ID>
join HD_TICKET_CHANGE_FIELD CF on C.ID = CF.HD_TICKET_CHANGE_ID
join USER PREVIOUSOWNER on CF.BEFORE_VALUE = PREVIOUSOWNER.ID
left join USER NEWOWNER on CF.AFTER_VALUE = NEWOWNER.ID
CF.FIELD_CHANGED = "OWNER_ID"
- Email each recipient in query results
Subject: [TICK:$ticket_number] $ticket_title
Column containing email addresses: EMAILCOLUMN
Message: Ticket number $ticket_number - "$ticket_title" has changed owner from you to $new_owner_name.
- Frequency: on Ticket Save