/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hi! I am new to the forum and a new kace admin. Thanks in advance for help with this question.

Is there a way to have an email alert sent if a ticket is not closed within a certain timeframe?
0 Comments   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

Answers

0
Yes, you can do this with a custom ticket rule by running it on a schedule to check the opened time of the ticket. If it is beyond your threshold, you can trigger an email.
Answered 06/24/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks for the response, I must be doing something wrong, I created a rule and selected time open > 19m , I use test and it shows some results , I have a ticket that has been open 7h but when i change the rule to 20m I get no results, if i change the rule to 1h 59m I then get results , if I change it to 2h I get no results. So I fished the rule using 19m but when I run it with the option to email the results in a table, I get an email that is blank. I basically am trying to create a rule to email helpdesk managers in the event a ticket isnt closed in a certain time , say 4 hours after it is created to meet an SLA.
Answered 06/24/2011 by: rodsmith01
Senior Yellow Belt

Please log in to comment
0
Post your SQL select and update statements, and I'll do my best to assist you.
Answered 06/27/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks Andy!! Here is what i have for the basic.


select HD_TICKET.*, 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,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_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 USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_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_TICKET.TIME_OPENED > '4h') AND HD_STATUS.NAME != 'closed') and HD_TICKET.HD_QUEUE_ID = 1 )
Answered 06/27/2011 by: rodsmith01
Senior Yellow Belt

Please log in to comment
0
You can't use '4h' as a value, because it's just passing it as a string. That is why you see it functioning sporadically and incorrectly.

Instead of (HD_TICKET.TIME_OPENED > '4h'), try replacing it with (TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_OPENED, NOW()))/60 > 240). The 240 represents 4 hours in minute form. That is why you can see I've divided the time in seconds by 60 to obtain a minute comparison.

Make sure the rule is set to run every 15 minutes, and not OTS. Unfortunately, this will mean it may be up to 4 hours and 15 minutes before the alert triggers.
Answered 06/27/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thank you Andy!!! I have quite a bit to learn about writing these queries.
Answered 06/27/2011 by: rodsmith01
Senior Yellow Belt

Please log in to comment

Share