I'm trying to get some custom ticket rules to trigger email events. I don't know SQL at all. Here are the rules I want. I think if I could create one of them or see the SQL, I could change the variables to create the rest, but I don't know if the wizard can really do this or if it needs some SQL magic.

If a ticket is in status "New" for 7 days then Charlie gets an email with ticket info.
If a ticket is in status "New" for 14 days then Lisa gets an email with ticket info.
If a ticket is in status "Follow-up" for 10 days then Charlie gets an email with ticket info.
If a ticket is in status "Follow-up" for 20 days then Lisa gets an email with ticket info.

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Since the Frequency is set to "1 Hour" won't Charlie get an email every hour once the 7 day threshold has passed? (Assuming the ticket status doesn't change)
Please log in to comment

Answers

1

Give this a shot - you can remove/edit the last line of the Select query as desired to restrict it to a specific queue (currently just set to focus on queue 1):

Title:
"New" for 7+ Days > Charlie

Order:
10

Notes:
Sends an email to Charlie when a ticket has sat in queue with a status of "New" for at least 7 days.

Frequency:
1 hour

Select Query:
Select 'Charlie@Company.Com' 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
______________________________

X Send an email for each result row
______________________________

ATTENTION - Ticket New for 7+ Days

Email Column:
CHARLIE

Email Body:
Charlie,

There's a ticket with status "New" for at least 7 days, please review.

Created:    $created
Stalled:        $modified
Status:         $status
Ticket ID:    $id
Issue:        $issue
Submitter:    $submitter
Owner:          $owner

Also, if you need help with SQL queries, I wrote an article that might help:

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

And here's an article covering my IT queue, with more ticket rules, so you can see some more examples:

http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

Hope that helps!

John

Answered 01/10/2013 by: jverbosk
Red Belt

  • Thanks a lot. I think I can create the rest with this. I read your tips article, but the primer looks very helpful. I will let you know if this works. thanks again.
Please log in to comment
Answer this question or Comment on this question for clarity