How to prevent rule from firing when conditions have not been met?
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
-
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 - Hobbsy 6 years ago
Answers (1)
Please log in to answer
Posted by:
jmarotto
6 years ago
"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.
