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>))
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
It looks good to me. How do you know it's not waiting until the 3rd day? How long is it waiting? Can you demonstrate that? Do you have any other rules that are modifying the MODIFIED column?
Answered 08/24/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 08/25/2011 by: scarpent
Second Degree Black Belt

Please log in to comment
0
ok i see why...i didn't look at the update because you originally said it is not waiting until the 3rd day before it sends an email and changes the status which 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?
Answered 08/25/2011 by: GillySpy
Seventh Degree Black Belt

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

Share