k1000 Due date warning ticket rule with email
Hi,
I just want to create a rule that emails the ticket owner and submitter when a process ticket is due.
I am setting up a Process that creates child tickets for Projects ie. for each stage of a project, a ticket is created with a due date to track each step of the project.
I am trying to modify the Overdue ticket rule to do this but I am not a SQL writer so I am a bit confused.
Any help is appreciated. I looked here and I didn't find anything that made sense to me that would do what I am hoping.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
ellisha
8 years ago
This is the code I eventually got working to get the rule to return the correct information.
Posted by:
Hobbsy
8 years ago
You are on the right track, to build the rule you need to have knowledge of the fields that you are going to use, namely the Due field and also an understanding of the warning targets that you want to give.
Let's assume that you only have one priority against which your project tickets are logged.
First I'd suggest you create a custom field that has an OK or Late Single select, lets call it an SLA Flag
Then we can build a rule using the wizard that says:
If the Ticket is not closed and the Priority is the default priority and the SLA Flag is OK and Due Date is NULL then update the SLA Flag field to Late
That should give you some SQL code in your rule with a where statement at the bottom a bit like this
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 != 'Closed') AND HD_PRIORITY.NAME = 'medium') AND HD_TICKET.CUSTOM_FIELD_VALUE5 = 'OK') AND HD_TICKET.DUE_DATE is null) and HD_TICKET.HD_QUEUE_ID = 3 )
Next Create a simple report using the report wizard, the reason we do this is because the reporting wizard has much more comprehensive Date based operators. So in the final filter stage select if the Due date is after now
Once you have saved your report go back in and view the SQL of the report and you will now see the SQL code for the Due Date statement
(HD_TICKET.DUE_DATE > now() )
Which you can now insert into your ticket rule SQL to give you the correct functionality
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 != 'Closed') AND HD_PRIORITY.NAME = 'medium') AND HD_TICKET.CUSTOM_FIELD_VALUE5 = 'OK') AND (HD_TICKET.DUE_DATE > now() )) and HD_TICKET.HD_QUEUE_ID = 3 )
Finally I suggest you copy the rule and set one to run on a 15mins schedule and the second to run ON Ticket Save to try and get the SLA Flag to change as accurately as possible
There are similar details in a Blog posted a little while back
http://www.itninja.com/blog/view/make-dell-kace-servicedesk-sla-s-easier-to-manage
Comments:
-
We are not currently using any of the SLA fields. Will I have to set that up to make this work? - ellisha 8 years ago
-
My initial response does not require SLA's to be configured to work, it should work fine with manually entered due dates - Hobbsy 8 years ago
-
Okay. I have a rule created to change the SLA field to Warning and late, depending on time. However, I cannot figure out how to make it change it to late when there is say 2 days to the due date. I tried "(((TIMESTAMP(HD_TICKET.DUE_DATE) >= NOW() AND TIMESTAMP(HD_TICKET.DUE_DATE) < DATE_ADD(NOW(),INTERVAL 2 DAY))))" but I cannot make it work.
Here is the code I am using to make the change to Warning. As you can see to get this properly working, I need to make changes to the DUE_Date field but do not know how:
select HD_TICKET.*,
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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_CATEGORY.NAME = 'Project Process') AND HD_STATUS.NAME != 'Closed') AND HD_TICKET.CUSTOM_FIELD_VALUE9 = 'OK') AND HD_TICKET.DUE_DATE is not null) and HD_TICKET.HD_QUEUE_ID = 1 ) - ellisha 8 years ago
-
This is the code I am having problems with if anyone can help
(TIMESTAMP(HD_TICKET.DUE_DATE) >= NOW() AND TIMESTAMP(HD_TICKET.DUE_DATE) < DATE_ADD(NOW(),INTERVAL xx HOUR/DAY)) - ellisha 8 years ago