/build/static/layout/Breadcrumb_cap_w.png

K1000: Prevent Email/Ticket Reopen on Computer/Asset deletion

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*************************


Comments

  • I'd love to use this, but get the following error in the Run Log regarding to the Update 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
  • John,
    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
  • What about the default ticket rule "ReopenTicket", that reopens a ticket if a change is made to it. Does this stop that, or do I need to edit that rule as described at the bottom here: http://www.kace.com/support/resources/kb/article/How-to-Use-Ticket-Rules-To-Suppress-Emails-About-Machine - edwimb 11 years ago
  • A ticket can still be reopened by a response to it this only stops it if an asset (user or machine) is unassigned - jdornan 11 years ago
  • The default ticket rule "ReopenTicket" also suffers from the problem of the tickets reopening if the machine is deleted. We have experienced it here.

    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
    • edwimb,

      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 just had this happen. The code I added to the reopen ticket rule shown above did prevent the ticket from reopening, but on any helpdesk that has the comment email on event category checked, they still get an email when the machine is deleted. I need to prevent this.

        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 10 years ago
  • Does this custom ticket rule work with 5.4? - BaumB83 10 years ago
  • Edwimb, so what code did you enter to stop the emails and suppress the tickets from reopening? I created a rule with the exact code in this thread and than created a ticket as a user and than deleted the user. The ticket didn't reopen so I assume the rule worked, but I still got an email that got triggered for that ticket with a comment that the user was deleted:

    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
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