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:


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,
                        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
        (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


Please log in to comment


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:
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

Please log in to comment
Answer this question or Comment on this question for clarity