/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Ticket Rule: CustomerResponded

10/30/2020 188 views

Hello,

I would like to modify the built in CustomerResponded ticket rule. Currently the rule changes the status of the ticket to "Opened" if anyone other than the ticket owner responds to the ticket. I would like the rule to change the status of the ticket to "Opened" if anyone other than a Queue Owner responds to the ticket. Can someone please advise on how to do this? Current SQL commands below.


Select SQL:

select distinct HD_TICKET.ID, 

                       HD_TICKET.OWNER_ID as OWNER_ID, 

                       HD_TICKET.ID as TICKNUM, 

                       HD_TICKET.TITLE,

                       HD_STATUS.NAME AS STATUS_NAME,

                       HD_STATUS.STATE as STATE,

                       OWNER.USER_NAME as OWNER_NAME,

                       OWNER.FULL_NAME as OWNER_FULLNAME,

                       OWNER.EMAIL as OWNER_EMAIL,

                       UPDATER.USER_NAME as UPDATERNAME,

                       UPDATER.EMAIL as UPDATEREMAIL

                  from (HD_TICKET, HD_STATUS)

             left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

             left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID

             left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

             left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID

                 where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID

                   and HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID

                   and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'

                   and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'

                   and HD_STATUS.ID in (3,6,7,8) 

                   and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL) 

                   and UPDATER.ID > 0 


Update SQL:

update HD_TICKET as T, HD_STATUS as STATUS 

set T.HD_STATUS_ID = STATUS.ID, 

T.RESOLUTION = CONCAT(T.RESOLUTION,'

Reopened'), 

T.TIME_OPENED  = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED), 

T.TIME_CLOSED  = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED), 

T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED), 

T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING), 

T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT) 

where STATUS.NAME = 'Opened' and 

T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))



Any help is appreciated, Thanks!

Eric

2 Comments   [ + ] Show comments

Comments

  • Can you define what you feel is the difference between a ticket Owner and a queue owner may be? As the where statement in your SQL "(UPDATER.ID <> OWNER.ID or OWNER.ID is NULL) " is set so that if anyone other than the ticket owner responds the ticket is updated ?
  • Yes, and thank you for the response!

    For us, the queue owner is any user that can be targeted by the Owner Label under Service Desk > Configuration > Queue Detail, under the User Preference header - Owner Label. This is a list of users who are allowed to work that Service Desk Queue.

    The ticket owner would be the specific user from that same label who has been selected on a specific ticket from the Owner drop down menu.

Be the first to answer this question

 
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