Why aren’t these Ticket rules doing what I think they should do?
I think it is supposed to work something like this. Ticket Owner makes a comment on an open ticket and the status should change to “waiting on customer”. If a customer has not responded to an owners comment within 3 days, they will get an email that says they need to reply within the next 4 days,(it is now in “Waiting Overdue”). If they did not respond after that (7 days), the ticket is closed and status will change to “overdue closed”. If the customer responds even weeks later their response automatically reopens the ticket, the status should change to “reopened”.
If the customer replies to the owner before the ticket is closed, it should change to “customer responded”.
When the submitter adds a comment, it should not change to “waiting on customer” since the submitter is the customer. But it does.
If this is how the process should work, the rules are not working correctly. Help is appreciated.
Waiting on customer rule:
select C.ID
FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN HD_STATUS S on HD_STATUS_ID=S.ID
JOIN HD_TICKET_CHANGE_FIELD FCOM ON FCOM.HD_TICKET_CHANGE_ID=C.ID and FCOM.FIELD_CHANGED='COMMENT'
LEFT JOIN HD_TICKET_CHANGE_FIELD FSTAT ON FSTAT.HD_TICKET_CHANGE_ID=C.ID AND FSTAT.FIELD_CHANGED='STATUS_NAME'
WHERE
FSTAT.ID IS NULL
and OWNERS_ONLY=0
and S.NAME<>'Waiting On Customer'
AND DESCRIPTION NOT LIKE 'TICKET CREATED%'
Update Query:
update HD_TICKET as T JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID
JOIN HD_STATUS S ON T.HD_QUEUE_ID=S.HD_QUEUE_ID
JOIN HD_STATUS S_OLD ON HD_STATUS_ID=S_OLD.ID
set DESCRIPTION=CONCAT(DESCRIPTION,'\nOwner Reply Triggered a Status Change. [Rule]\nChanged ticket Status from "',S_OLD.NAME,'" to "',S.NAME,'"'),
HD_STATUS_ID=S.ID
where
S.NAME='Waiting On Customer' and
(C.ID in (<TICKET_IDS>))
Waiting Overdue
Moves ticket into an overdue status when it has been waiting on a customer action for a period of time
Select Query:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID and ((HD_STATUS.NAME = 'Waiting on Customer'))
and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
and HD_TICKET.HD_QUEUE_ID = 1
Comments: If no update is received in the next 4 days, the ticket will be closed automatically.
In general all support requests should include the following information:
Contact information,
Site/Location
Computer affected,
Detailed description of the problem,
and best time to contact.
If this information is not provided you may experience considerable delay in the resolution of your support request.
Regards,
NMUSD Help Desk
Update Query:
update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
where T5.NAME = 'Waiting Overdue' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
CustomerResponded Rule
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 (25,9)
and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)
and UPDATER.ID > 0
X Send an email for each result row
Subject: Ticket $ticknum - Customer Responded
Email Column: OWNER_EMAIL
Ticket $ticknum owned by "$owner_name" has been reopened because the customer or other non-owner made a ticket update.
You may review the ticket here:
http://kbox1200.<nmusd>.us/adminui/ticket?ID=$ticknum
The status was "$status_name" and is now marked as "Reopened".
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 = 'Reopened' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
ReopenTicket rule
Notes: Reopens a closed ticket if someone other than the owner updates it, and notifies the owner by email
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
Comment: This closed ticket was updated by a non-owner. Changing status to "Reopened."
Note: If you were trying to close this case: You MUST take ownership to close a case.
Subject: Ticket $ticknum Reopened
Email Column: Closed ticket $ticknum owned by "$owner_name" has been reopened.
You may review the ticket here:
http://kbox1200.<nmusd>.us/adminui/ticket?ID=$ticknum
The status was "$status_name" and is now marked as "Reopened".
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 = 'Reopened' and
T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
Answers (1)
This is a lot to look at. For the first rule that changes the status to waiting on customer you are only selecting C.ID which selects comment IDs. The update rule only acts on Ticket IDs, however, so I don't think that will work. You need to select ticket IDs and then just change the ticket's status. I have never tried modifying the comment to add a description like you are attempting. Instead I use the function in the rule to add a comment that the rule ran. This also helps separate out the actions and makes it more clear that the rule ran, in my opinion.
