Another simple rule, but can be usefull for somebody who is starting with Dell Kace. when a ticket is closed, if the user send a note before one month since it was closed, then the ticket is reopen, if it is after one month, then the tickets is not reopen. in both cases the user receive and notification by email explaining the situation.

1 case - ReopenTicket-1

-> Select SQL

select distinct HD_TICKET.ID,
               HD_TICKET.ID as TICKNUM, 
               HD_TICKET.TITLE,
               HD_STATUS.NAME AS STATUS_NAME,
               UPDATER.FULL_NAME as UPDATERNAME,
               USUARIO.EMAIL as SUBMITTER_EMAIL,
               USUARIO.FULL_NAME AS USUARIO_NAME,
               IDFilho,
               HD_TICKET.TIME_CLOSED,
               timestampdiff (DAY,HD_TICKET.TIME_CLOSED ,CURDATE()) AS DIFERENCIA, 
               timestampdiff(DAY, DATE_SUB(CURDATE(), INTERVAL 1 MONTH), curdate()) AS MaxDias
         
from HD_TICKET
     inner join HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID   
     INNER join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
     INNER JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
     INNER join USER USUARIO on USUARIO.ID = HD_TICKET.SUBMITTER_ID
  
inner Join ( Select TC.HD_TICKET_ID, max(TC.ID) IDFilho from HD_TICKET_CHANGE TC inner join HD_TICKET_CHANGE_FIELD F 
        on ( F.HD_TICKET_CHANGE_ID = TC.ID)  Group by TC.HD_TICKET_ID ) X on ( X.HD_TICKET_ID = HD_TICKET.ID )
 inner Join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = X.IDFilho)
 inner Join HD_TICKET_CHANGE HTC  on ( X.IDFilho = HTC.ID)

WHERE
    F.FIELD_CHANGED = 'COMMENT' and
    HD_TICKET.SUBMITTER_ID = UPDATER.ID AND
    HD_STATUS.STATE = 'CLOSED'
   AND timestampdiff (DAY,HD_TICKET.TIME_CLOSED ,CURDATE()) < timestampdiff(DAY, DATE_SUB(CURDATE(), INTERVAL 1 MONTH), curdate())

group by HD_TICKET.ID

-> Active email checkbox
Assunto:Tíquete [$ticknum] $title Reaberto 
Coluna contendo endereços de e-mail:SUBMITTER_EMAIL
Mensagem:XXX

->Actualized SQL

update HD_TICKET 
JOIN HD_STATUS ON HD_STATUS.NAME = 'Reaberto'  and HD_TICKET.HD_QUEUE_ID = HD_STATUS.HD_QUEUE_ID

set HD_TICKET.HD_STATUS_ID = HD_STATUS.ID, 

HD_TICKET.TIME_OPENED  = IF(HD_STATUS.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 
HD_TICKET.TIME_CLOSED  = IF(HD_STATUS.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 
HD_TICKET.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED) 

WHERE HD_TICKET.ID IN (<TICKET_IDS>) 


2 case - ReopenTicket-2

->Select SQL

select distinct HD_TICKET.ID,
               HD_TICKET.ID as TICKNUM, 
               HD_TICKET.TITLE,
               HD_STATUS.NAME AS STATUS_NAME,
               UPDATER.FULL_NAME as UPDATERNAME,
               USUARIO.EMAIL as SUBMITTER_EMAIL,
               USUARIO.FULL_NAME AS USUARIO_NAME,
               IDFilho,
               HD_TICKET.TIME_CLOSED,
               timestampdiff (DAY,HD_TICKET.TIME_CLOSED ,CURDATE()) AS DIFERENCIA, 
               timestampdiff(DAY, DATE_SUB(CURDATE(), INTERVAL 1 MONTH), curdate()) AS MaxDias
         
from HD_TICKET
     inner join HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID   
     INNER join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
     INNER JOIN HD_STATUS ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
     INNER join USER USUARIO on USUARIO.ID = HD_TICKET.SUBMITTER_ID
  
inner Join ( Select TC.HD_TICKET_ID, max(TC.ID) IDFilho from HD_TICKET_CHANGE TC inner join HD_TICKET_CHANGE_FIELD F 
        on ( F.HD_TICKET_CHANGE_ID = TC.ID)  Group by TC.HD_TICKET_ID ) X on ( X.HD_TICKET_ID = HD_TICKET.ID )
 inner Join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = X.IDFilho)
 inner Join HD_TICKET_CHANGE HTC  on ( X.IDFilho = HTC.ID)

WHERE
    F.FIELD_CHANGED = 'COMMENT' and
    HD_TICKET.SUBMITTER_ID = UPDATER.ID AND
    HD_STATUS.NAME = 'CLOSED'
AND timestampdiff (DAY,HD_TICKET.TIME_CLOSED ,CURDATE()) > timestampdiff(DAY, DATE_SUB(CURDATE(), INTERVAL 1 MONTH), curdate())

   
group by HD_TICKET.ID

-> Active email checkbox
Assunto:Tíquete [$ticknum] $title Reaberto 
Coluna contendo endereços de e-mail:SUBMITTER_EMAIL
Mensagem:XXX