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



9 Comments   [ - ] Hide 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.
  • 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
  • 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.
  • 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.
  • 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...
  • 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.
  • 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!
  • I have implemented it 2 years ago... checking it again to post it here
  • Hi Twit, did you mange to get this code working?
    • 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!
Please log in to comment

Answer this question or Comment on this question for clarity



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

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

Answered 07/30/2013 by: afzal
Fourth Degree Green Belt

  • Is this done on ticket save?
    • I should be on Ticket Update, However you should try it at some test queue
      • There is no Run on Ticket Update in the list, and this code isn't working for me. I will investigate some more code.
Please log in to comment