KACE Product Support

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'


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. 

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.

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,
===========================================================================================

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.

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.

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.

How many queues do you have?
What are the priority names and timeouts for your priorities with Escalation times?

We have queue that we use. I'm asking Mike Pace to answer your other questions (via this forum).

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!

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

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"

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?

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)

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 :)

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.

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.

Just and FYI,

The queries worked.

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?

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.