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

Please log in to comment

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

Please log in to comment
Answer this question or Comment on this question for clarity

Share