/build/static/layout/Breadcrumb_cap_w.png

K1000 Escalation Email Rules

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.


1 Comment   [ + ] Show comment
  • 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) - lauriejstone 10 years ago

Answers (1)

Answer Summary:
Posted by: jverbosk 11 years ago
Red Belt
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


Comments:
  • 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. - lmland 11 years ago

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