Prevent email loop - Please help
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,
0 Comments
[ + ] Show comments
Answers (2)
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
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
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
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.
Comments:
-
Thank you Chuck. Is there a way to use this as a ticket rule and change the submitter to unassigned or the owner? - bozadmin 7 years ago
-
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. - chucksteel 7 years ago-
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? - bozadmin 7 years ago -
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>) - bozadmin 7 years ago
-
That would probably work better. You can also adjust the interval to 15 MINUTE instead of 1 HOUR (and yes, that is minute singular). - chucksteel 7 years ago
-
Yes, you can reset the CC_LIST also. - chucksteel 7 years ago
Posted by:
mvarnado
7 years ago
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.
