/build/static/layout/Breadcrumb_cap_w.png
07/12/2017 626 views
We created a ticket rule that tests if a ticket is in a specific Status AND if specific fields have been completed, particularly "not null". There are three other rules in place, the first of which checks the status, that certain fields have been completed, then it sends out an email and updates the status (similar to the one experiencing the problem). The issue we are experiencing is the second rule is firing right after the first one has completed; it updates the status and sends out the email. This is wrong because the fields that are supposed be "not null" are still null, therefore, the rule should not be firing. I've pasted the code below, any suggestions on how I can fix it? I am lost at this point. 

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(HD_TICKET.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 = 'Awaiting Department Comments') AND HD_TICKET.CUSTOM_FIELD_VALUE31 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE32 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE33 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE34 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE35 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE36 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE37 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE38 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE39 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE40 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE41 is not null) AND HD_TICKET.CUSTOM_FIELD_VALUE42 is not null) AND HD_TICKET.HD_QUEUE_ID = 50)

1 Comment   [ + ] Show comment

Comments

  • It may be that you have a bug in your KACE build we have recently seen instances where ticket rules no longer fire in order despite being set. You will need to do some simple testing to confirm this is not the case

All Answers

0
"is not null' may not valid if the field value is set to text. Try !='' that's two single quotes. I've run into to this type of issue before.
Answered 07/12/2017 by: jmarotto
Third Degree Green Belt