At the moment, when the submitter of a closed ticket replies to that ticket, it does not re-open the ticket, nor alert the owner. This means comments following ticket closure are missed. Is there a rule that can be created to make the closed ticket re-open and alert the owner once a comment is made but not the user satisfaction survey?


Thanks in advance!

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
New ticket rule from Service Desk > Configuration > Rules > New (SQL):

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.STATE = 'closed' 
           and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL) 
           and UPDATER.ID > 0

-----

Select Append comment to ticket (if you'd like) and enter whatever information you want).
Select Email each recipient in query results;
- Enter ticket subject (mine is: Ticket $ticknum Reopened);
- Enter OWNER_EMAIL for Column containing email addresses:
- Enter message (mine says: Closed ticket $ticknum owned by "$owner_name" has been reopened. You may review the ticket here: http://kbox/adminui/ticket$id=$ticknum. The status was "$$status_name" and is now marked as "Reopened".)

Select Run update query and enter in:

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 = 'Reopened' and 
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))

-----

Set the schedule to run on Ticket Save.

Answered 11/13/2014 by: jfrank
Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share