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   [ + ] 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.
  • 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



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
Answer this question or Comment on this question for clarity