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

Comments

Please log in to comment

Answers

0
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
Answered 10/27/2014 by: h2opolo25
Red Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity