Article: 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