Comment Threshold Lock
Flatten email recipients on tickets suspected of looping between email systems.
SELECT
HD_TICKET.ID, CHANGECOUNT,
CONCAT('https://YOUR.KACE.URL/adminui/ticket?ID=',ID) as LINK
FROM
(SELECT
COUNT(C.ID) as CHANGECOUNT,
HD_TICKET.ID as TKID
FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
GROUP BY HD_TICKET.ID
HAVING COUNT(C.ID) > 75) CC
JOIN HD_TICKET HD_TICKET ON HD_TICKET.ID = TKID
WHERE CC.CHANGECOUNT > 75
Check off email results, enter recipients.
Append owners only comment to ticket:
This ticket is locked due to excessive comments. If these comments are not in error, please reach out to the system administrator. As a loop precaution, the client and any CC's have been reset.
update HD_TICKET, USER as T6
set HD_TICKET.CC_LIST = 'reporter@YOUR.KACE.URL',
HD_TICKET.SUBMITTER_ID = T6.ID
where T6.USER_NAME = 'reporter@YOUR.KACE.URL' and
(HD_TICKET.ID in (<TICKET_IDS>))
Of course in general, manual intervention is best, but this should assist in some automated catching.
We also use a rule that looks for known offending email addresses that flattens any tickets generated from a known do-not-reply, internal devices etc email address.
Additionally we have a loop alert rule that looks for excessive ticket changes over time and sends a flag if it crosses the threshold of activity typical for the organization.
Loop Alert
This code checks for looping tickets by notifying staff if a tickets updates exceeds a set threshold per given time.
SELECT 'COMMA SEPARATED,EMAIL,RECIPIENTS' AS TLOOP,
SUSPECT as SUSPECTT
FROM
(SELECT MAX(HD_TICKET_ID) AS SUSPECT
FROM
(SELECT
COUNT(TIMESTAMP) AS COUNTT, HD_TICKET_ID
FROM HD_TICKET_CHANGE WHERE TIMESTAMP> DATE_SUB(NOW(), INTERVAL 15 MINUTE )
)XYT2
WHERE COUNTT>60)XYT3 /*This will be different for every organization and will take some playing*/
WHERE SUSPECT LIKE '%%'
;
Email each recipient in query results
Loop Alert, column TLOOP
✏ Please check into into possible email looping ticket https://YOUR.KACE.URL/adminui/ticket?ID=$suspectt
If looping change requester asap to reporter@YOUR.KACE.URL
Comments