/build/static/layout/Breadcrumb_cap_w.png
10/31/2016 1203 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