/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Ticket Rule to Send Email on Ticket owner Assignment

04/18/2016 1249 views
I am trying to create a Ticket rule to send an email to a Ticket Owner, when a ticket is assigned to an Owner

This is the query I have so far.


SELECT
HD_TICKET.ID AS TICKNUM,
(SELECT FULL_NAME FROM USER WHERE USER.ID=HD_TICKET.SUBMITTER_ID) AS SUBMITTER,
HD_TICKET.TITLE AS TITLE,
HD_TICKET.CREATED AS CREATED,
U.FULL_NAME AS OWNER,
U.EMAIL AS NEWTICKETEMAIL,
TQ.NAME AS QNAME,
TS.NAME AS STATUSNAME,
TCF.FIELD_CHANGED,
TCF.BEFORE_VALUE,
TCF.AFTER_VALUE,
TCF.ID AS CHANGEID
FROM
HD_TICKET
LEFT JOIN USER U ON U.ID = HD_TICKET.OWNER_ID
LEFT JOIN HD_STATUS TS ON TS.ID = HD_TICKET.HD_STATUS_ID
LEFT JOIN HD_QUEUE TQ ON TQ.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN HD_TICKET_CHANGE TC ON TC.HD_TICKET_ID = HD_TICKET.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD TCF ON TCF.HD_TICKET_CHANGE_ID = TC.ID
WHERE
TS.NAME != 'Closed'
AND TCF.FIELD_CHANGED = 'OWNER_ID'
AND TC.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM HD_TICKET_CHANGE WHERE HD_TICKET_CHANGE.ID=TC.ID)
AND TCF.ID=<CHANGE_ID>



0 Comments   [ + ] Show comments

Comments


All Answers

0
You don't have to check the TC.TIMESTAMP value, so you can remove that line. You should also be using TCF.HD_TICKET_CHANGE_ID , not TCF.ID. The change ID supplied by the appliance is stored in HD_TICKET_CHANGE_ID, not ID for the HD_TICKET_CHANGE_FIELD table.

Answered 04/19/2016 by: chucksteel
Red Belt

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