Custom Ticket Rule to Send Email on Ticket owner Assignment

Question

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>

Answer

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.

Custom Ticket Rule to Send Email on Ticket owner Assignment

Answers (1)

Posted by:

View more:

Related Questions

Related Links

Related Posts

KACE Product Support

Custom Ticket Rule to Send Email on Ticket owner Assignment

Answers (1)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share