Systems Management

I have the "Customer Responded" rule changing ticket status successfully. I am also looking for a way to create a rule that can change Customer Responded, Opened and Reopened statuses to change to "Tech Responded" when a ticket owner add a comment. Any help or suggestions appreciated!

Here is the “Customer Responded” rule I am using.

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 (10,32)

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

                   and UPDATER.ID > 0

Run Update Query:

 update HD_TICKET as T, HD_STATUS as STATUS

set T.HD_STATUS_ID = STATUS.ID,

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 = 'User Responded' and

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

 Frequency: On Ticket Save