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!

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Be sure to follow the Precautions for Creating Ticket Rules!

This should do it:

  1. Create a New (SQL) ticket rule
  2. Give it a name and optional Description
  3. 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"

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

  5. Frequency: on Ticket Save
Answered 04/08/2015 by: PHKace
Blue Belt

  • Thanks a million!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity