KACE Product Support

I am looking to create a few rules that will change a status to a reopened status when the user responds. For some reason it does not work. I am sure I am not changing something that needs to be changed. Could someone look this over and let me know what I am doing wrong? Basically, this rule will take a ticket that is currently in the "Opened" status and change it to the "Reopened" status when the user adds a comment and saves it. It works on another status but not any other ones I try it with. This is why I think I need to change something that I am unaware of.


Select Query

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
LEFT JOIN /*eligible owners*/(select EO.ID,Q.ID QID FROM HD_QUEUE Q JOIN HD_QUEUE_OWNER_LABEL_JT QL ON Q.ID=QL.HD_QUEUE_ID JOIN LABEL L ON L.ID=QL.LABEL_ID JOIN USER_LABEL_JT UL ON UL.LABEL_ID=L.ID JOIN USER EO ON EO.ID=UL.USER_ID) EOS ON UPDATER.ID=EOS.ID AND EOS.QID=HD_TICKET.HD_QUEUE_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) and EOS.ID IS NULL
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0


Update Query

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>))