Ticket rule sent an email ONLY when the end user respond
Hello, we are trying to re-create a ticket rule that stopped working.
This rule will apply only when the end-user submits a comment to the ticket via portal or email. We don't want to receive an email if one of our techs (ticket owners) comments.
The email containing the last ticket comment will be sent to OWNER_EMAIL.
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,
HD_TICKET_CHANGE.COMMENT as last_comment,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.FULL_NAME as UPDATER_FULLNAME,
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 (47)
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,' '),
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 = 'Responded' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
Any help will be appreciated.
Answers (1)
Top Answer
So basically in your ticket rule, if the ticket is updated and the update is not linked to the customer satisfaction survey and is not made by a ticket owner AND the ticket is in status ID 47 then the rule will fire.
So if it’s not working I would start with what Status ID 47 is?
Run a report
SELECT * FROM HD_STATUS
To find out what that status is
Comments:
-
Hi Hobbsy,
Status 47 is "Follow Up." When this status is set, the ticket will close 7 days after the last ticket update from the user.
The intended functionality is that when the end-user replies to our ticket, its status will change to "Responded," and our Tech team will receive an email notification.
Here's the issue: We receive an email when our tech comments on the ticket and also changes its status to "Responded." We don't want that. We want this rule to be triggered by end-users, not ticket owners.
I appreciate your help and guidance! - horacior 1 month ago-
Maybe change the Owner line in the select where statement to when the owner is not in and then an array with all your each user id’s?
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL - Hobbsy 4 weeks ago
