/build/static/layout/Breadcrumb_cap_w.png

Stop tickets closed more than X days ago from being re-opened

Hi, here's one for all you SQL experts out there.

I would like to test the idea of stopping tickets that were closed more than say 3 days ago, from being able to be re-opened. The idea being that a user couldn't just find an old helpdesk email and reply to it as a way of asking a new questions etc.

I got the theory of what I want OK, but I'm just not good enough at this SQL business to implement it myself:

On ticket save:

IF time closed exists but IS NOT within last 3 days AND status != "Closed - Resolved", change status to "Closed - Resolved" and email TICKET_OWNER and TICKET_SUBMITTER:

"Hello, an attempt was made to re-open ticket $ticknum however the original resolution date is more than 3 days ago. Tickets that were closed more than 3 days ago cannot be re-opened. If you attempted to re-open this ticket or replied to an email regarding this ticket, please log a new ticket with the helpdesk." (wording to be confirmed of course).

 

-Will this cause the ticket closed email to be re-sent? In my experience if a rule changes a ticket status, it doesn't trigger other rules based on status change.

-What select queries do I need like HD_TICKET.ID AS TICKNUM etc including being able to use OWNER_EMAIL and SUBMITTER_EMAIL in the email address field.

-Need to make sure this doesn't cause any email loops

 

Thanks.


9 Comments   [ + ] Show comments
  • This is a great question and great rule. I am going to work on it for our own Help Desk and post the solution i find. This should be totally doable. - lmland 10 years ago
  • Just to give you a head start. I'm thinking you make the select query based on WHERE STATUS like "Closed%" (we have a "Closed no response" status also) AND DATEDIFF(NOW(), HD_TICKET_CHANGE.TIMESTAMP) > 3 - lmland 10 years ago
  • You're going to have to have a custom rule for sending the closed emails and turn off the default one. Otherwise it will send another closed email anytime someone tries to reopen an old ticket. - lmland 10 years ago
  • After working on this I'm not sure, BUT I would also like this functionality in our Help Desk. I'll post an answer if i find it. - lmland 10 years ago
  • Hi Imland, thanks for your responses so far. Hopefully you can manage to come up with something. I have a custom rule for nearly everything as I found the built-in rules actually weren't that reliable. It's most likely that I will have to create a new custom rule for ticket closed like you say, but I did once accidentally re-open all tickets ever logged (OOPS!) but that didn't trigger an email, I think because if status changed in background, it's not treated the same as clicking 'Save'. Just a guess, but it definitely didn't trigger any emails in this event.

    Anyway, thanks for your efforts and I'll check back soon... - twit 10 years ago
  • Don't feel bad twit. I once set the ticket Priority to Critical on 3200+ tickets, 600 or so were open active tickets :D Hey, it happens. - jmarotto 10 years ago
  • Haha, thanks jmarotto, not that I want to see others making mistakes but good to know I'm not the only one ;) Thank god for backups hey! - twit 10 years ago
  • I have implemented it 2 years ago... checking it again to post it here - afzal 10 years ago
  • Hi Twit, did you mange to get this code working? - chris.poston 9 years ago
    • Hi Chris, sorry I forgot about this post if I'm honest. The reason for that the company I work for won't exist much longer (long story, parent companies involved etc). Nevertheless, our KACE units days are just as numbered so I stopped development on it. Good luck with this though!
      Cheers - twit 9 years ago

Answers (1)

Posted by: afzal 10 years ago
Fourth Degree Green Belt
2

Reopen Ticket if responded in 72 hours.

1. Create a custom Help Desk rule in Kbox
2. Type the following query in the  ‘Select SQL query’
3. Change the hours as required , in the highlighted  text of SQL ,  say 72 if you wish to reopen the ticket on in 72 hours.  [Time_Closed))<1]
4. Type the Update query in the ‘ Update query ‘
5. Select the Check boxes as shown in screen shot
6. Test it in some Test Queue, dont forget to change the queue ID in the SQL

 

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 = 'Closed') AND hour(TIMEDIFF(now(),Time_Closed))<1 and OWNER_ID<>(SELECT USER_ID FROM HD_TICKET_CHANGE where HD_TICKET_ID=HD_TICKET.ID order by TimeStamp desc limit 1)) and HD_TICKET.HD_QUEUE_ID = 1 )


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


Comments:
  • Is this done on ticket save? - chris.poston 9 years ago
    • I should be on Ticket Update, However you should try it at some test queue - afzal 9 years ago
      • There is no Run on Ticket Update in the list, and this code isn't working for me. I will investigate some more code. - chris.poston 9 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ