Ticket Rule Creation for Resolved Status
We don't want our techs to close a ticket we want it to go into a Resolved Status and then after 3 days go to Waiting Overdue etc. until the system automatically closes the ticket and sends out the email.
I have created a custom ticket rule for this but it is not waiting until the 3rd day before it sends an email and changes the status. I am pasting my query below. What am I missing? I am brushing up on my SQL so I am sure the change is obvious
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 = 'Resolved'))
and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
and HD_TICKET.HD_QUEUE_ID = 1
update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'
Closed Email Sent'),
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 = 'Closed' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
I have created a custom ticket rule for this but it is not waiting until the 3rd day before it sends an email and changes the status. I am pasting my query below. What am I missing? I am brushing up on my SQL so I am sure the change is obvious
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 = 'Resolved'))
and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
and HD_TICKET.HD_QUEUE_ID = 1
update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'
Closed Email Sent'),
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 = 'Closed' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))
0 Comments
[ + ] Show comments
Answers (3)
Please log in to answer
Posted by:
GillySpy
13 years ago
Posted by:
scarpent
13 years ago
I have tickets that were put in the resolved status and have not moved to the next status and it has been past 3 days. When I ran the rule on some of the tickets it changed them to responded.
Here is the flow that I would like to have happen:
Resolved- sits 3 days and then auto moves to Waiting Overdue
Waiting Overdue moves to Overdue Close
Email on Close
We are not using the email feature because of the way it works with the owners only box and using the technicians personal outlook account when you click on email on incident.
We are not using the reopen ticket. Once a ticket closes the client has to open a new ticket.
Here is the flow that I would like to have happen:
Resolved- sits 3 days and then auto moves to Waiting Overdue
Waiting Overdue moves to Overdue Close
Email on Close
We are not using the email feature because of the way it works with the owners only box and using the technicians personal outlook account when you click on email on incident.
We are not using the reopen ticket. Once a ticket closes the client has to open a new ticket.
Posted by:
GillySpy
13 years ago
ok i see why...i didn't look at the update because you originally said
However, because you are now saying that they are not moving then that could suggest a problem with either the select or update.
Your update is written to move the tickets into a closed status and not a waiting overdue status.
What is the frequency of your rule? I would suggest at least daily after hours or more frequently.
Secondly some questions:
it is not waiting until the 3rd day before it sends an email and changes the statuswhich to me is the same as saying that it is sending earlier than 3 days. This would suggest a problem with the select.
However, because you are now saying that they are not moving then that could suggest a problem with either the select or update.
Your update is written to move the tickets into a closed status and not a waiting overdue status.
What is the frequency of your rule? I would suggest at least daily after hours or more frequently.
Secondly some questions:
- how do you run the rule "on [only] some of the tickets"?
- Do you have any other rules in place, for example the "custom responded" system rule?
- What is the purpose of mention the email feature -- are you having a problem with that?
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
so that the conversation will remain readable.