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.
Thanks!
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
PHKace
9 years ago
Top Answer
Be sure to follow the Precautions for Creating Ticket Rules!
This should do it:
This should do it:
- Create a New (SQL) ticket rule
- Give it a name and optional Description
- Select SQL:
select
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
from
HD_TICKET
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
where
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
Comments:
-
Thanks a million! - Rayman 9 years ago
