/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: ellisha 8 years ago
Yellow Belt
0
This is the code I eventually got working to get the rule to return the correct information.  

((((( HD_CATEGORY.NAME = 'Project Process') AND HD_STATUS.NAME != 'Closed') AND HD_TICKET.CUSTOM_FIELD_VALUE9 = 'OK') and HD_TICKET.HD_QUEUE_ID = 1)  AND  TIMESTAMP(HD_TICKET.DUE_DATE) <= DATE_ADD(NOW(),INTERVAL 5 DAY)) 
Posted by: Hobbsy 8 years ago
Red Belt
0
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

1k1biY.png

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ