K1000 rules: Just ONE notification on ticket rule
Hello everyone!!
I have been reading and i didnt find what im looking for. i have a ticket rule for escalation. it sends and email to SUPPORT when a ticket has been untouched for 1:30h. that works perfectly, and thanks to GillySpy it works just on working hours, from 8:00 to 18:00, from monday to friday and not in christmas neither new year.
MY PROBLEM is that it sends a notification every one hour and a half if it is still untouched. i want just one notification to dont fill SUPPORT inbox with the same email of the same ticket. does anybody know the instruccion, or the solution?? maybe is in the frequency (i have it On Ticket Saved)???
->SELECT SQL
select 'somebody@yourcompany.br' as SUPPORT, HD_TICKET.TITLE AS TITLE,HD_TICKET.ID AS ID,
HD_STATUS.NAME AS STATUS,Q.NAME as QUEUE,
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,
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.MODIFIED) end as AGE,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL
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
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.CREATED = HD_TICKET.MODIFIED
and HD_STATUS.STATE != 'CLOSED'
and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 )
and HOUR(NOW())>=8 /* 8am or later kbox time*/
and CURTIME()<='18:00:00' /* before 6pm Pacific */
and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') /* not the weekend */
and DAYOFYEAR(NOW()) NOT IN (1,360) /* not xmas or new year's */
GROUP BY HD_TICKET.TITLE, HD_TICKET.ID
->active the tickbox for sending and email for every destinary of the consult
-Tittle:Escalating
-Email:SUPPORT (here is where you choose the person or people who is gonna receive the alert. you have to write it in the select query)
-Message:
Tickets não atribuidos em 1:30h.
Creado: $sort_time_created
Titulo: $title
Ticket ID: $id
Solicitante: $submitter_fullname
Corpo do chamado: http://youraddressforservicedesk/adminui/ticket.php?ID=$id
Thank you!!!
2 Comments
[ + ] Show comments
-
You could create a custom field that indicates if a message has been sent but then you would also need a rule to unset that once the ticket gets modified again. If you really want the ticket to get attention, however, then leaving the rule as is would encourage the technicians to act on the ticket. - chucksteel 9 years ago
-
i dont understand this properly...probably because of my ignorance, but i see it a bit mess, because what i want is an email to a centain person when a ticket has been totally totally untouched - Paloma 9 years ago
Answers (3)
Answer Summary:
I found the real solution from Wildwolfay and i guess that it is the same solution that chucksteel were talking about, but i didnt understand. I add a counter in my where query, one of the custom field (i keep it hide), that has to be on 0. and in the update i increase this counter. The notification and the rest is the same, so i am gonna just add the where statements and the update. 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.CREATED = HD_TICKET.MODIFIED and HD_STATUS.STATE != 'CLOSED' and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 ) and HD_TICKET.CUSTOM_FIELD_VALUE1 = 0 #Contador and HOUR(NOW())>=8 /* 8am or later kbox time*/ and CURTIME()<='18:00:00' /* before 6pm Pacific */ and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') /* not the weekend */ and DAYOFYEAR(NOW()) NOT IN (1,360) /* not xmas or new year's */ ->Update SQL update HD_TICKET set HD_TICKET.MODIFIED = NOW(), HD_TICKET.CUSTOM_FIELD_VALUE1 = HD_TICKET.CUSTOM_FIELD_VALUE1 + 1 where HD_TICKET.ID in ()
I found the real solution from Wildwolfay and i guess that it is the same solution that chucksteel were talking about, but i didnt understand. I add a counter in my where query, one of the custom field (i keep it hide), that has to be on 0. and in the update i increase this counter. The notification and the rest is the same, so i am gonna just add the where statements and the update. 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.CREATED = HD_TICKET.MODIFIED and HD_STATUS.STATE != 'CLOSED' and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 ) and HD_TICKET.CUSTOM_FIELD_VALUE1 = 0 #Contador and HOUR(NOW())>=8 /* 8am or later kbox time*/ and CURTIME()<='18:00:00' /* before 6pm Pacific */ and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') /* not the weekend */ and DAYOFYEAR(NOW()) NOT IN (1,360) /* not xmas or new year's */ ->Update SQL update HD_TICKET set HD_TICKET.MODIFIED = NOW(), HD_TICKET.CUSTOM_FIELD_VALUE1 = HD_TICKET.CUSTOM_FIELD_VALUE1 + 1 where HD_TICKET.ID in ()
Please log in to answer
Posted by:
Paloma
9 years ago
I found the SOLUTION!!!, probably there is another better, but this works!!
The frequency of the rule is 15 minutes, so to be sure that the notification is gonna fix inside this 15 minutes independently of the ticket.modified time, the conditions has to be:
(you can do this also with a between and it is nicer)
and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400'
and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) < '6240' # entre 1:30h-1:44h
so send a notification if the difference between now and time modied is between 1:30h and 1:44h.
in this way with the first condition (X > 1:30h) once the ticket is untouched more than 1:30h, the first time that the rule works, it sends the notification.
the second condition (X < 1:44h) makes that the next time the rule is gonna work, the ticket doesnt meet all the conditions, so no more notifications are send!! :-)
Comments:
-
Im so sorry people, but this query doesnt work properly neither, it has some conflicts.
the point is that the rule just work during workly hours, so if a ticket is send from 16:30 the rule should act on it at 18:00 (and send the notification), but from 18:00 till 8:00 of the next day it doesnt work, but at 8:00 from the next day the untouched ticket has more than 1:44 h (i set from 1:30 to 1:44) so the notification is not send. - Paloma 9 years ago
Posted by:
Paloma
9 years ago
I found the real solution from Wildwolfay and i guess that it is the same solution that chucksteel were talking about, but i didnt understand.
I add a counter in my where query, one of the custom field (i keep it hide), that has to be on 0. and in the update i increase this counter.
The notification and the rest is the same, so i am gonna just add the where statements and the update.
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.CREATED = HD_TICKET.MODIFIED
and HD_STATUS.STATE != 'CLOSED'
and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 )
and HD_TICKET.CUSTOM_FIELD_VALUE1 = 0 #Contador
and HOUR(NOW())>=8 /* 8am or later kbox time*/
and CURTIME()<='18:00:00' /* before 6pm Pacific */
and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') /* not the weekend */
and DAYOFYEAR(NOW()) NOT IN (1,360) /* not xmas or new year's */
->Update SQL
update HD_TICKET
set HD_TICKET.MODIFIED = NOW(),
HD_TICKET.CUSTOM_FIELD_VALUE1 = HD_TICKET.CUSTOM_FIELD_VALUE1 + 1
where HD_TICKET.ID in (<TICKET_IDS>)
Posted by:
Paloma
9 years ago
i have thought this solution, i am still not sure if it works properly, im still testing.
i put the frequency every 15 minutes and in the routine compare the time between the last modification and the actual time, it sends the notification if this difference is between 1:30h and 1:40h. in this way it sends just one notification (the difference are 10 minutes, and the rule acts every 15).
I put this in my where conditions:
and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 )
and unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) < '5620' # between 1:30h-1:40h in secs.
if i use this instructions separately, it works. toguether....not properly... maybe i am not understanding the instructions.