ISSUE: When you delete a computer from Inventory all tickets in helpdesk send notification emails
RESOLUTION: Apply the following Custom Ticket Rule.
Setup as below.
**********************SELECT QUERY************************
select -- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
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,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
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 HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_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
( C.COMMENT LIKE '%Machine % was deleted%' OR C.COMMENT LIKE 'User % was deleted%')
and HD_STATUS.STATE = 'Closed'
***********************END SELECT*************************
**********************UPDATE QUERY************************
update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS='', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM
OWNERS_ONLY_DESCRIPTION), '\n',
'Ticket Rule: Suppressing email notification for user/machine deletion'
,'\n') ,'\n\n','\n')))
where
(HD_TICKET_CHANGE.ID = (<CHANGE_ID>))
************************END QUERY*************************
55:19> Starting: Tue, 19 Feb 2013 11:55:19 -0500
55:19> Executing Select Query...
55:19> selected 1 rows
55:19> Executing Update Query...
55:19> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6] in EXECUTE("update HD_TICKET_CHANGE
set HD_TICKET_CHANGE.NOTIFY_USERS='',
OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH 'n' FROM(REPLACE (CONCAT(TRIM(BOTH 'n' FROM OWNERS_ONLY_DESCRIPTION), 'n',
'Ticket Rule: Suppressing email notification for user/machine deletion' ,'n') ,'nn','n')))
where HD_TICKET_CHANGE.ID in ()")
55:19> Ending: Tue, 19 Feb 2013 11:55:19 -0500
__________________________________________
I tried several variations of the Update query and adding the columns specified in the Update query to the Select query, but continue getting the error:
select HD_TICKET.ID, HD_TICKET.ID AS TICKNUM, HD_TICKET.TITLE, HD_TICKET_CHANGE.ID,
HD_TICKET_CHANGE.NOTIFY_USERS, HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED,
HD_TICKET_CHANGE.COMMENT, HD_TICKET_CHANGE.DESCRIPTION,
GROUP_CONCAT(CONCAT('----- Change at ',HD_TICKET_CHANGE.TIMESTAMP,' -----\n',
HD_TICKET_CHANGE.DESCRIPTION,'\n',
HD_TICKET_CHANGE.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',
HD_TICKET_CHANGE.HD_TICKET_ID,'\n')
ORDER BY HD_TICKET_CHANGE.ID DESC SEPARATOR '\n') HISTORY
FROM HD_TICKET
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.ID=<CHANGE_ID>
WHERE (HD_TICKET_CHANGE.DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR HD_TICKET_CHANGE.DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%'
OR HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%')
and ( HD_TICKET_CHANGE.COMMENT LIKE '%Machine % was deleted%'
OR HD_TICKET_CHANGE.COMMENT LIKE 'User % was deleted%')
and HD_TICKET.HD_STATUS_ID = 2
update HD_TICKET_CHANGE, HD_TICKET
set HD_TICKET_CHANGE.NOTIFY_USERS='',
HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION), '\n',
'Ticket Rule: Suppressing email notification for user/machine deletion' ,'\n') ,'\n\n','\n')))
where HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
AND HD_TICKET.ID in (<TICKET_IDS>)
06:50> Starting: Tue, 19 Feb 2013 12:06:50 -0500
06:50> Executing Select Query...
06:50> selected 1 rows
06:50> Executing Update Query...
06:50> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 7] in EXECUTE("update HD_TICKET_CHANGE, HD_TICKET
set HD_TICKET_CHANGE.NOTIFY_USERS='',
HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH 'n' FROM(REPLACE (CONCAT(TRIM(BOTH 'n' FROM HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION), 'n',
'Ticket Rule: Suppressing email notification for user/machine deletion' ,'n') ,'nn','n')))
where HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
AND HD_TICKET.ID in ()")
06:50> Ending: Tue, 19 Feb 2013 12:06:50 -0500
__________________________________________
Any ideas/suggestions?
John - jverbosk 11 years ago
The ticket rule actually worked as is but generated an error when running on tickets that it DIDN'T apply to. This has to do with the auto created <TICKET_IDS> field not being populated.
Notice the error line AND HD_TICKET.ID in () shows the empty variable ()
I had trimmed out a lot of the fields I was not accessing to make the rule lean however kace expects them to generate its part so I added them back. Error now doesn't appear when using. - jdornan 11 years ago
I want tickets to still be reopened via an email response.
I went ahead and just added the following code to my select query in "ReopenTicket". I hoping by doing this I don't need your custom ticket rule at all.
and DESCRIPTION NOT LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
and DESCRIPTION NOT LIKE '%Changed Ticket Submitter from "%" to Unassigned%'
and COMMENT NOT LIKE '%Machine % was deleted%'
and COMMENT NOT LIKE 'User % was deleted%'
I'll let you know how it works out. - edwimb 11 years ago
I added those lines a few weeks ago to a copy of ReoOpen ticket—disabling the original Re-Open Ticket rule—and it suppresses a ticket re-opening when I delete a user or inventory/asset. Today, however, I found out that it will still send out an email to the ticket submitter because the deletion of the asset counts as a Comment, and submitters are set to receive notifications about comments that are not set to Owners-Only.
My question (as I don't know SQL at all) is whether jdornan's custom rule will also suppress emails to the ticket submitter when a change is made. If so, is it a rule I use in addition to my custom Re-Open Ticket rule or one that replaces it? If it's in addition to, what should be the Order of this new rule? My current Re-Open ticket is set to 120, like the rule illustrated above.
Basically, I need to suppress both the re-opening of a ticket and emails sent to the submitter.
Thanks. - KFox 11 years ago
I'm going to try a combination of my edited reopen ticket rule as well as jdornan's rule listed above. My reopen ticket rule should stop the status from changing, and jdornan's rule should stop the comments.
Update 6/24/13: FYI, this worked. - edwimb 11 years ago
edit: I think I got the email because we have "Any Change" set for owner so that's why I got the email for the comment. - glennvelsol 10 years ago