/build/static/layout/Breadcrumb_cap_w.png

K1000 rules: Reopen ticket

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


Comments

This post is locked

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