KACE Product Support : Query for status update "Need more information"

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

0 Comments [ + ] Show comments

Answers (1)

Posted by: h2opolo25 10 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 10 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question