Has anyone written a ticket rule that limits reminder emails to ticket owners to certain hours, for instance, 8-5 M-F? If so, may I steal it from you?

Thanks ~ David
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
You can simply duplicate the built-in rule that you want to restrict, and then add another statement to the WHERE clause to restrict the rule to only apply during certain hours on certain dates. I don't have this written, but I can certainly help if you aren't a SQL guru.
Answered 11/28/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I think this is where to change this? (Service Desk > Configuration > Help Desk Email Customization > Ticket Escalation). Where would the WHERE statement be added?



Open Ticket Reminder:
While this $ticket_priority priority ticket remains open, these emails will be sent every $ticket_escalation_minutes minutes.


To update this ticket at any time, or add information click here: $ticket_url
You may also reply to this email without changing the subject to update the ticket.
(Please use a blank message body to avoid duplicating information in the ticket.)


You can also contact the Helpdesk team at Extension 5584.


===========================================================================================
Ticket Details:
Ticket Info: $ticket_number, $ticket_title,
Submitter Info: $ticket_submitter_name, $ticket_submitter_email,
Ticket History:
$ticket_history,
===========================================================================================
Answered 11/29/2010 by: Jiddle
Orange Senior Belt

Please log in to comment
0
You can't modify the SQL behind the "Escalation" rule. However, you can uncheck "Escalation" for all groups, and then create a custom ticket rule in any queue to perform the escalation emails within a specified time frame.

I've got an idea as to how to go about this. I've started writing the SQL and it seems possible. The only limitation is that you're basically stuck with 15 minutes or 1 hour for the rule's interval. You can't go by the escalation timer set on each priority, because ticket rules have to run on a set schedule (or on ticket save, but that wouldn't work for this).

I'll post a walkthrough for you if you can deal with the 15 or 60 minute escalation limitation.
Answered 12/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Actually, I just had another idea... Two simple custom SQL rules - one for the beginning of the email "window" and one for the end. The first would set the escalation timeouts for all priorities that require escalation reminders. The second rule would run at the end of the day to set all escalation timeouts to 0. This would precisely disable the escalation emails between a specific time frame. The only drawback is that this change would be global to the queue. If you only send reminders to Owners, then this would work great.
Answered 12/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
That sounds great, Andy! If it's not too much trouble, we could really use your help. I think this could benefit other companies using the KACE appliances as well.
Answered 12/01/2010 by: Jiddle
Orange Senior Belt

Please log in to comment
0
How many queues do you have?
What are the priority names and timeouts for your priorities with Escalation times?
Answered 12/01/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
We have queue that we use. I'm asking Mike Pace to answer your other questions (via this forum).
Answered 12/01/2010 by: Jiddle
Orange Senior Belt

Please log in to comment
0
Hi Andy, I work with Jiddle and he has asked me to try and answer your questions.

How many queues do you have?
Just one entitled "Systems Support Helpdesk".

What are the priority names and timeouts for your priorities with Escalation times?

High 4 hours
Medium 1 day
Low 3 days

We also have these escalation rules in place:

15 Minute Escalation
60 Minute Escalation
3 Day Escalation

Let me know if I didn't give you enough or I gave you the wrong information. Thanks a lot for the help!
Answered 12/01/2010 by: MikePace
Senior Yellow Belt

Please log in to comment
0
Hi,

I'm having the same issue. It drives some of our tech staff crazy with all the escalation emails.

Different times.

Urgent: 30 minutes
High: 1 hour
Medium: 2 hours
Low: 8 hours
Answered 12/01/2010 by: londeaux
Green Belt

Please log in to comment
0
Disclaimer: I haven't tested any of this, but I've written it based on my experience. I'm fairly confident it will work properly, but use at your own risk.

Turning them off is easy. WARNING: Do NOT create/enable the rule to turn off the escalation emails before you create the rule to turn them back on. Otherwise, you will lose your escalation minute values if you haven't written them down somewhere!

To disable the notifications in a specific window, create a custom ticket rule set to run every 15 minutes with the following select and update queries:

Select Query (change the bolded hours to reflect your queue number and off-hours schedule... it is now set to only stop escalation emails between 5pm and 8am):
SELECT ID FROM HD_PRIORITY
WHERE (ESCALATION_MINUTES != 0) AND (HOUR(NOW()) < 8 OR HOUR(NOW()) > 17) AND HD_QUEUE_ID = 1

Update Query:
UPDATE HD_PRIORITY
SET HD_PRIORITY.ESCALATION_MINUTES = 0
WHERE (HD_PRIORITY.ID in (<TICKET_IDS>))

To turn them back on, you must create a rule for each priority you have with an escalation time using the following Select and Update queries. Make sure the select query window is the opposite of the disable timeout rule! You can't do this part in one rule, because the KBOX won't allow multiple statements in a ticket rule - you can only update one priority at a time... unless they are all going to have the exact same escalation time.

Select Query:
SELECT ID FROM HD_PRIORITY
WHERE (ESCALATION_MINUTES = 0) AND (HOUR(NOW()) > 8 AND HOUR(NOW()) < 17) AND HD_QUEUE_ID = 1

Update Query (change the bold values for the specific priority escalation this rule will re-enable):
UPDATE HD_PRIORITY
SET ESCALATION_MINUTES = 30
WHERE NAME = "High"
Answered 12/02/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Is it possible to constrain it to certains of the week? Like it's only on Monday through Friday or on off on Saturday and Sunday?
Answered 12/02/2010 by: londeaux
Green Belt

Please log in to comment
0
Just add the following to the WHERE clause for the enable rules to exclude Saturday and Sunday:

AND DAYOFWEEK(NOW()) not in (1,7)

i.e.

UPDATE HD_PRIORITY
SET ESCALATION_MINUTES = 30
WHERE NAME = "High" AND DAYOFWEEK(NOW()) not in (1,7)
Answered 12/02/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
All- it's important to remember that built-in "Escalations" is basically a reminder service; where MikePace's example referred to "escalation rules". Those are custom ticket rules (that Kace built for them long ago) that are reassigning tickets to different techs automatically if they remain in an open state for those time periods. Those rules actually contain all the SQL needed to to day of week and time of day if Jiddle or MikePace are in the sharing mood :)
Answered 12/02/2010 by: cblake
Red Belt

Please log in to comment
0
Thanks for the week stuff. I'm going to pester you one last time on this and leave you alone on this subject.

There was an article that discussed out of office loops where we can limit the number of email recieved. Is it possible to restrtict the number of times an email is sent out based upon the above rule? For SLA purposes. Like it sent out 5 emails and the ticket status hasn't been changed, it sends an email to the owner's supervisor or reassigns the ticket to a group.
Answered 12/02/2010 by: londeaux
Green Belt

Please log in to comment
0
Londeaux, you'd have to keep track of the number of emails sent and add that field with a max value to the WHERE clause. I'm sure this is possible (using assets or a custom field somewhere... like a custom ticket field that's hidden), but it would be messy.

cblake, thanks for the explanation...

Jiddle and MikePace, in order for me to help modify the custom rules KACE gave you, you'd have to post the SQL here. It sounds like what you're looking for has been built into your custom rules all along. Ignore the solution I've posted, as it is designed for standard ticket escalation notifications and not the custom rules KACE gave you.
Answered 12/02/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Just and FYI,

The queries worked.
Answered 12/02/2010 by: londeaux
Green Belt

Please log in to comment
0
I lied I'm going to bug you one more time.

Closed office days, like holidays. Would we need to setup a separate turn off rule for them?
Answered 12/02/2010 by: londeaux
Green Belt

Please log in to comment
0
You just need to add something like this to the WHERE clause:

WHERE CONCAT(MONTH(NOW()) + "-" + DAYOFMONTH(NOW())) like "12-25"

This example would apply to Christmas.
Answered 12/03/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
Haha sorry, I didn't know these were written just for us; I came onto the scene after these were in place.

Before we get lost in semantics, I think I may be able to apply the code airwolf has already posted to meet our needs.


As for sharing our custom ticket escalation rules, below is the SQL for the 15 minute escalation rule that will reassign a new ticket that lies dormant for 15 minutes:

select HD_TICKET.*, 'USERNAME@bgark.com' NEW_OWNER, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and ((( HD_STATUS.NAME like '%New%') AND HD_TICKET.CREATED < DATE_SUB(NOW(),INTERVAL 14 MINUTE)) and HD_TICKET.HD_QUEUE_ID = 1 )
and U1.USER_NAME = ('USERNAME') and
TIME(NOW()) > '08:30:00' and TIME(NOW()) < '17:00:00'
Answered 12/06/2010 by: MikePace
Senior Yellow Belt

Please log in to comment
1
Thought I would shed light on this after 5 years. :-) This can now be done by setting the business hours. By doing that emails will only be sent during business hours. 
Answered 11/25/2015 by: JC_Chi
Senior White Belt

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