/build/static/layout/Breadcrumb_cap_w.png

Kace ticket loop alert

For those of you who have problems with tickets looping either from do-not-reply's [E.g. Do-not-reply notification -> ticket update notification -> do-not-reply notification -> ticket-update-notification-> etc.] or internal rules gone awry, here's some code to help alert you to the loops:

SELECT

'YOUREMAILADRESS@TEST.COM' 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 1 MINUTE )
)XYT2
WHERE COUNTT>10)XYT3
WHERE SUSPECT LIKE '%%'
;

You will want to enter your own email address to recieve the alert and edit the threshold above to match your volume. If you have a very high voilume environment, its possible that you may have more than 10 interactions with tickets per minute on average.

To base this number, use the following:

-can be pasted as a SQL report in the repord tab

-Update the timeframe to match your busiest time, divede out from there to get a rate per minute to adjust the threshold above.

SELECT  
COUNT(TIMESTAMP) AS COUNTT, HD_TICKET_ID
FROM HD_TICKET_CHANGE WHERE TIMESTAMP> DATE_SUB(NOW(), INTERVAL 30 DAY )

Once you have the threshold set, setup the top rule in a test queue (disclaimer, not liable for any code, always  test before using live!!)

Set the rule to run every fifteen minutes, and to email for results row. Email column as TLOOP and body can be something like:

**Please check into into http://YOUR.KBOX.DOMAIN/userui/ticket?ID=$suspectt

Subject can be whatever grabs your attention.

Then test and adjust the threshold as needed, play with it. Suggestions for improvement welcome, but here's a start.

Nest step could be to create a rule to update the requestor in the event of a do-not-reply loop to a dummy or owner account to halt the loop, but this should at least alert you that there may be a problem to investigate.


Comments

  • another way to look for looping tickets is by this report. This looks at cumulative interactions on a ticket, a looper would show an excessive amount of interactions on a ticket. Could be a daily/weekly/monthly check.

    SELECT HD_TICKET_ID,
    COUNT(HD_TICKET_ID) as TOTAL
    FROM `ORG1`.`HD_TICKET_CHANGE`
    LEFT JOIN USER UPDATER ON UPDATER.ID = USER_ID
    GROUP BY HD_TICKET_ID ORDER BY TOTAL DESC LIMIT 30; - tholmes 9 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