/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 Service Desk create rule to email owner on ticket due/overdue

04/12/2016 898 views

I'm banging my head against the wall here.  I want to create a rule that emails the ticket owner when a ticket is due/overdue and set the SLA flag to 'LATE.  I tried creating a rule based on the instructions below:

http://www.itninja.com/question/k1000-overdue-ticket-rule

I've tested this numerous times, and no email was sent out and no email sent to the ticket owner.

Here is the rule SQL code:

select HD_TICKET.*,
                        HD_TICKET.ID as TICKET_ID,
                        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_STATUS.NAME != 'Closed') AND HD_PRIORITY.NAME = 'medium') AND HD_TICKET.CUSTOM_FIELD_VALUE0 = 'OK') AND HD_TICKET.DUE_DATE > now()) and HD_TICKET.HD_QUEUE_ID = 1 )


**Update code***

update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE1 = 'LATE'
  where
        (HD_TICKET.ID in (<TICKET_IDS>))


the 'email each recipient in query results' is checked with 'OWNER_EMAIL' typed in the 'column containing email addresses' box.

I have one more question.  Should the SLA Flag be set to 'Owners Only - Hidden from users'?  I tried setting it to read only with 'OK' as the default, but nothing is displayed under SLA Flag when I ticket is created.  I even tried the 'Owners Only - Hidden from users' option and left it at the default 'OK' setting, but that didn't work either.


Please advise.



0 Comments   [ + ] Show comments

Comments


All Answers

1
Your select statement looks for CUSTOM_FIELD_VALUE0 = 'OK' but then you are updating CUSTOM_FIELD_VALUE1. Remember that the column names in the database are 0 based, so custom field 1 in the UI is CUSTOM_FIELD_VALUE0 in the database.

You're also looking for tickets where the due date is greater than now. I would recommend comparing dates directly:
DATE(HD_TICKET.DUE_DATE) < DATE(NOW())
That should find tickets where the due date is before today.

When in doubt, enable the option to email the query results to a user and have them emailed to you. That will give you a list of the matching results and will help debug your query.
 
Answered 04/13/2016 by: chucksteel
Red Belt

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