/build/static/layout/Breadcrumb_cap_w.png

Stop Kace Ticket Email Loops

Here's a way to stop tickets from looping for instance when an internal process creates an email into the service desk, but that process does not actually have a mailbox to receive the reply emails. Needs to act on ticket save. A little tricky using the internal variables and system added ticket where information. Note, could only get this to work using the Email results, not the email results to recipient in column. Can't change the initial select columns as using the kace internal variables to pass the correct information for the correct ticket on save. 

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

  • Great article. Thanks! - JasonEgg 8 years ago
This post is locked
 
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