I have ticket rules that email a supervisor if a ticket sits in "New" or "Follow-up" status for a specified period of time. I want to create another rule that is identical except it emails a different supervisor if his employees have tickets in "New" or "Follow-up" status for the same perios of time. In other words, we have two Departments (networking and technicians) that want the same notification rules. I created user labels to distinguish between the two departments, but don't know how to select that label in the ticket rule. We don't want separate queues for these departments, because of our size (small).

Any ideas on either how to write a ticket rule that includes sorting ticket owners by user label or a better way to do what I want?

Here's an example of the current query based only on status and time, not differentiating between owners:

Select 'cfrey@wacoisd.org' AS CHARLIE,

S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE,

U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER,

T.CREATED AS CREATED, T.MODIFIED AS STALLED

FROM HD_TICKET T

JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)

JOIN USER U ON (U.ID = T.SUBMITTER_ID)

JOIN USER O ON (O.ID = T.OWNER_ID)

WHERE (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) >= 7)

AND T.HD_QUEUE_ID = 1

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I recognize that query...  ^_^

Yes, this should be possible using a subquery (I prefer inline views, where possible) to select for tickets that meet the employees' ticket status criteria, which then passes those results up to the main query (where the supervisor's criteria can be specified).  Take a look at this article for how to create/use inline views:

http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables

As for including user labels (assuming you have some), you can use the USER_LABEL_JT to join the USER and LABEL tables.  There are a number of reports here on ITNinja that you can reference for examples.

If you get stuck, post what you end up.

Hope that helps!

John

Answered 01/18/2013 by: jverbosk
Red Belt

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