/build/static/layout/Breadcrumb_cap_w.png

K1000 Service Desk: How to select user label in ticket rule

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: jverbosk 11 years ago
Red Belt
1

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ