Scripting Question

Custom Ticket Rule to Change Owner Problem

02/18/2016 1345 views
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? 


EDIT: SQL code

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,
                        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 )
update HD_TICKET, USER as T5
  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*/
5 Comments   [ + ] Show 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.

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ