/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


SQL Script to Send an Email to Previous Ticket Owner.

04/02/2015 1635 views

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:
0 Comments   [ + ] Show comments

Comments


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!

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ