I have an issue when running a custom ticket rule to change the owner on ticket save.  If the user is of the department Commercial Operations, I send the ticket to Technician1.  IF the user is not of this particular department, the rule doesn't run and the default owner of ticket is assigned. 

The department field is a textbox that is populated on ticket save (grabs the LDAP custom field that was imported). 
Department rule is Order 5; Owner rule is Order 10. 

What I am seeing:
Administrator submits ticket on behalf of user - ticket rules perform job correctly (admin is connected to server through https://k1000/admin)
User submits ticket for themselves - ticket rules run, but owner change has a severe 'lag'.  This lag causes the default owner to be notified.  Technician1 never receives a notification even though they are the new ticket owner.  Of course they will be notified on an escalation event. 

What would be a solution to this issue?  Am I going about this wrong? 

Thanks!    

EDIT: SQL code

-------Select------------
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(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_TICKET.CUSTOM_FIELD_VALUE1 = 'Commercial Operations') AND DATE_FORMAT(HD_TICKET.CREATED, '%k%i') = DATE_FORMAT(HD_TICKET.MODIFIED, '%k%i')) and HD_TICKET.HD_QUEUE_ID = 5 )
-------Select------------
-------Update-----------
update HD_TICKET, USER as T5
    set HD_TICKET.OWNER_ID = T5.ID
  where T5.USER_NAME = 'Technician1' and 
        (HD_TICKET.ID in (TICKET_IDS)) /*this has greater-that less-than surrounding, but cannot get it to show on post*/
-------Update-----------
5 Comments   [ + ] Show Comments

Comments

  • Please post the SQL statements for the select and update operations of the rule that isn't working.
  • Hopefully I did that somewhat correctly...
  • I have found a solution to part of the issue, by running two rules and using a field that changes on save, I can get the correct owner assigned. Now the only issue I have is the incorrect emails that are sent. The owner is determined on save, but the default owner is notified instead...
  • Did you ever get a better solution? Which "email on events" settings do you have checked in the queue? Can you post the CTRs, their order, and their run interval?
  • I contacted support. I was advised that when using custom rules the email function is changed, due to the email functionality built into the rules. I had to create rules for each owner to assign the right category to the correct owner and send an email confirmation. Not the best solution, but a working one.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity