/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Prevent email loop - Please help

10/31/2016 1447 views
Please help I am tired of email loops - Does any one have a solution to prevent email loops? Over the last few years I have had some email loops but last few weeks it looks like I am dealing with 1 every other week. I have 8 queues and anytime someone updates an old ticket that submitter or cc-d user is no longer with the company it causes a loop. I tried 2 custom rules from support but those only to stop the loop and not prevent. 1 of Support tech' suggestion was to not delete email accounts (SMH). 

I need something like maybe a custom rule that if a ticket is updated too many times say in 25 minutes trigger alert or if ticket has more than 50 comments change the submitter and cc-d to unassigned.

I also tried this (http://www.itninja.com/blog/view/stop-kace-ticket-email-loops) but it doesnt change the submitter. I also think trying this rule caused me a loop cause it updated a ticket that had more than 50 comments with submitter's email no longer active.

Thank you,



Answer Summary:
SELECT HD_TICKET_ID, COUNT(ID) NumberUpdates FROM ORG1.HD_TICKET_CHANGE WHERE TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 HOUR) GROUP BY HD_TICKET_ID HAVING NumberUpdates > 15 ORDER BY COUNT(ID) DESC
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
Again, this won't prevent email loops but it will help detect them. This query will find tickets that have more than 15 updates in the past hour:
SELECT HD_TICKET_ID, COUNT(ID) NumberUpdates FROM ORG1.HD_TICKET_CHANGE 
WHERE
TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY HD_TICKET_ID
HAVING NumberUpdates > 15
ORDER BY COUNT(ID) DESC
I use it as an alert to let me know that there might be a possible loop. You could use it in a ticket rule that would also have an update statement to change the submitter.

Answered 10/31/2016 by: chucksteel
Red Belt

  • Thank you Chuck. Is there a way to use this as a ticket rule and change the submitter to unassigned or the owner?
    • Yes, that should be possible. Create a new ticket rule and use the statement above for the select statement. For the update statement use something like this:

      UPDATE HD_TICKET set SUBMITTER_ID = 0 WHERE ID in (<TICKET_IDS>)

      That would set the submitter to user 0, you might want to use a different user ID depending on your environment.

      Note that you will need a copy of this rule in every queue. You can't make rules that target all queues.
      • This is awesome thank you. I am hoping this is the last time I deal with horrible email loop issues.

        So I am guessing I will set the ticket rule to run ever 15 minutes or so instead of ticket save?
      • Can I also use this statement to change both the submitter and the cc? UPDATE HD_TICKET set SUBMITTER_ID = 0 and set HD_TICKET.CC_LIST = 0 WHERE ID in (<TICKET_IDS>)
      • That would probably work better. You can also adjust the interval to 15 MINUTE instead of 1 HOUR (and yes, that is minute singular).
      • Yes, you can reset the CC_LIST also.

All Answers

0
It's crude, but here's what I developed to deal with loops for the same reason - folks no longer with the company and OOO replies.

Select SQL:

SELECT 
    HD_TICKET.*,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL AS STATUS_ORDINAL,
    HD_IMPACT.ORDINAL AS IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL AS CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL AS PRIORITY_NUMBER,
    STATE,
    IF((DATEDIFF(DUE_DATE, NOW()) = 0),
        2,
        IF((DATEDIFF(DUE_DATE, NOW()) < 0),
            1,
            3)) AS SORT_OVERDUE_STATUS,
    IF(UNIX_TIMESTAMP(TIME_OPENED) > 0,
        TIME_OPENED,
        1 << 62) AS SORT_TIME_OPENED,
    IF(UNIX_TIMESTAMP(TIME_STALLED) > 0,
        TIME_STALLED,
        1 << 62) AS SORT_TIME_STALLED,
    IF(UNIX_TIMESTAMP(TIME_CLOSED) > 0,
        TIME_CLOSED,
        1 << 62) AS SORT_TIME_CLOSED,
    IF(UNIX_TIMESTAMP(ESCALATED) > 0,
        ESCALATED,
        1 << 62) AS SORT_ESCALATED,
    IF(UNIX_TIMESTAMP(HD_TICKET.CREATED) > 0,
        HD_TICKET.CREATED,
        1 << 62) AS SORT_TIME_CREATED,
    IF(UNIX_TIMESTAMP(HD_TICKET.MODIFIED) > 0,
        HD_TICKET.MODIFIED,
        1 << 62) AS SORT_MODIFIED,
    IF(UNIX_TIMESTAMP(HD_TICKET.DUE_DATE) > 0,
        HD_TICKET.DUE_DATE,
        1 << 62) AS SORT_DUE_DATE,
    CASE UPPER(STATE)
        WHEN 'CLOSED' THEN UNIX_TIMESTAMP(HD_TICKET.TIME_CLOSED) - UNIX_TIMESTAMP(HD_TICKET.TIME_OPENED)
        WHEN 'OPENED' THEN UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(HD_TICKET.TIME_OPENED)
        ELSE UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(HD_TICKET.CREATED)
    END AS AGE,
    IF((LENGTH(U1.FULL_NAME) = 0),
        U1.USER_NAME,
        U1.FULL_NAME) AS OWNER_NAME,
    U1.FULL_NAME AS OWNER_FULLNAME,
    U1.EMAIL AS OWNER_EMAIL,
    IF(U1.ID IS NULL,
        'z',
        CONCAT('a',
                IF((LENGTH(U1.FULL_NAME) = 0),
                    U1.USER_NAME,
                    U1.FULL_NAME))) AS SORT_OWNER_NAME,
    IF((LENGTH(U2.FULL_NAME) = 0),
        U2.USER_NAME,
        U2.FULL_NAME) AS SUBMITTER_NAME,
    U2.FULL_NAME AS SUBMITTER_FULLNAME,
    U2.EMAIL AS SUBMITTER_EMAIL,
    IF(U2.ID IS NULL,
        'z',
        CONCAT('a',
                IF((LENGTH(U2.FULL_NAME) = 0),
                    U2.USER_NAME,
                    U2.FULL_NAME))) AS SORT_SUBMITTER_NAME,
    IF(U3.ID IS NULL,
        'z',
        CONCAT('a',
                IF((LENGTH(U3.FULL_NAME) = 0),
                    U3.USER_NAME,
                    U3.FULL_NAME))) AS SORT_APPROVER_NAME,
    IF(APPROVAL = 'rejected',
        'Rejected',
        IF(APPROVAL = 'info',
            'More Info Needed',
            IF(APPROVAL = 'approved',
                'Approved',
                IF(APPROVER_ID > 0, 'Pending', '')))) AS APPROVAL_STATUS
FROM
    (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
        LEFT JOIN
    USER U1 ON U1.ID = HD_TICKET.OWNER_ID
        LEFT JOIN
    USER U2 ON U2.ID = HD_TICKET.SUBMITTER_ID
        LEFT JOIN
    USER U3 ON U3.ID = HD_TICKET.APPROVER_ID
        LEFT JOIN
    MACHINE M1 ON M1.ID = HD_TICKET.MACHINE_ID
WHERE
    HD_PRIORITY.ID = HD_PRIORITY_ID
        AND HD_STATUS.ID = HD_STATUS_ID
        AND HD_IMPACT.ID = HD_IMPACT_ID
        AND HD_CATEGORY.ID = HD_CATEGORY_ID
        AND HD_TICKET.MODIFIED >= DATE_SUB(NOW(), INTERVAL 120 SECOND)
        AND HD_TICKET.SUBMITTER_ID != 0
        AND HD_TICKET.SUBMITTER_ID != 1624
        AND HD_TICKET.SUBMITTER_ID != ''
        AND ((EXISTS( SELECT 
            1
        FROM
            HD_TICKET_CHANGE
        WHERE
            HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
                AND (HD_TICKET_CHANGE.COMMENT LIKE 'Delivery has failed%'
                OR HD_TICKET_CHANGE.COMMENT LIKE 'To whom it may concern,%'))))
        AND HD_TICKET.HD_QUEUE_ID = 47


NOTE:   The .COMMENT LIKE statements are text that are found at the beginning of our policy-applied auto-reply that goes out from termed accounts.

Email results:
=> my email so I know what happened

Comment:
(Owners Only)
Removed Submitter, Owner, and CC List due to suspected Comment Loop.  Ensure that future assignments of Owner, Submitter, and CC recipients are valid addresses.

Run Update Query:

UPDATE HD_TICKET,
    USER AS T5 
SET 
    HD_TICKET.SUBMITTER_ID = 0,
    HD_TICKET.CC_LIST = '',
    HD_TICKET.OWNER_ID = 0
WHERE
    T5.ID = 1624
        AND (HD_TICKET.ID IN (<TICKET_IDS>))




This kills off the Owner, Submitter, and CC.  I tried doing them one at a time and they just kept slipping through.

I realize this is a "big hammer" approach, but it catches 2-5 a week and prevents them from creating loops.

This development was prompted by our KBOX just slowing to a crawl, then investigation revealing over 100 tickets that were in continual loops - roughly 2 years after the appliance was turned on.  Frankly, once I figured out that there was NO native loop protection, I was impressed that it was only ~100.
Answered 11/02/2016 by: mvarnado
White Belt

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