Running a rule on a new ticket only once - upon first time creation
I have a need to run a rule ONLY ONCE on my primary ticket queue, specifically for tickets emailed in.
I want it to check for some key words in the Title and if they exist then move that ticket to another queue and update a few fields at the same time.
I do not want to run the rule on that ticket ever again.
How can I make this happen?
In my code below the rule always runs on the ticket no matter the team assignment.
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,
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp() - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp() - 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
INNER JOIN HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
INNER JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_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_TICKET.MODIFIED = HD_TICKET_CHANGE.TIMESTAMP
AND (((( HD_TICKET.TITLE like '%Apples1234%') OR HD_TICKET.TITLE like '%Peaches1234%') AND HD_TICKET.CUSTOM_FIELD_VALUE5 = 'Helpdesk') and HD_TICKET.HD_QUEUE_ID = 1 )
Then I am going to update a few fields moving the team assignment to another team.
This works great for emailed in requests to the ticket queue. The rule runs on the ticket the first time it sees it.
But if I go work on that ticket after that, and say I change the team assignment, I am finding the rule continues to run on this ticket and thus assigns the ticket back to the team assignment in the rule.
How can I prevent the rule from running on a ticket where it's already run once?
Here are a couple of ideas:
1. On your Update rule, you could use one of the CUSTOM_FIELD_VALUE 1-14 to be a marker to indicate the ticket was already updated and then change your Select query to check the value to see if you need to run it. That would be quick and dirty, but you would have to check the tables to see if a ticket is marked if you want to know if a ticket was affected by the rule.
2. You could also configure your rule to add a new comment with a distict phrase like:
<<--xx Re-Assigned to queue ABC xx-->>
and have the Select rule check to see if the ticket has any comments with that phrase and not run if it does. This is a little more difficult to setup, but it logs the event so you know it ran and have a record of the re-assignment.
I like to use FlySpeed query to visualy build out the Select statements because it give you the SQL in MySQL syntax.