Query for status update "Need more information"
Hi,
I am looking for a query to send an email to the submitter when the owner change the status to "Need more information". I am not a SQL-expert, so I hope someone can help me.
I saw I question before about this subject. But could someone expain it to me?
Has anyone fully query for me?
Thanks.
Rodin
I saw I question before about this subject. But could someone expain it to me?
Has anyone fully query for me?
Thanks.
Rodin
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
h2opolo25
9 years ago
You can do something like this...
select distinct(HD_TICKET.ID), U.EMAIL as EMAIL from HD_TICKET
left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID
left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
where CF.AFTER_VALUE = 'Need more information'
and CF.FIELD_CHANGED = 'STATUS_NAME'
and TC.TIMESTAMP > subdate(NOW(), INTERVAL 10 SECOND)
and HD_TICKET.HD_QUEUE_ID = 1
This will pull the ID and Submitter email from tickets that have been changed in the last 10 seconds to a status of Need more information from Queue 1.
Column containing email address: EMAIL
You can use the ID number in the subject or body of the email by using $id
Select 'On Ticket Save' as the schedule
Comments:
-
KACE provides a <CHANGE_ID> variable that you can use when crafting queries like this which allows you to join to the HD_TICKET_CHANGE table based on the change which was triggered when the ticket was saved. Using that variable the query would look like this:
select HD_TICKET.ID, U.EMAIL as EMAIL from HD_TICKET
left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID and TC.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
where CF.AFTER_VALUE = 'Need more information'
and CF.FIELD_CHANGED = 'STATUS_NAME'
and HD_TICKET.HD_QUEUE_ID = 1
Because we are using the <CHANGE_ID> variable we don't have to limit to changes that occurred only in the past 10 seconds. - chucksteel 9 years ago
