KACE Product Support Question

Custom Rule to send email notifications to either the Category_CC with multiple emails or a static email list

03/15/2019 673 views

I am unable to get the settings correct in the email settings to make sure that the category CC emails get a notification that a ticket was created with that category. I have tried the check boxes on the Service Desk Queue Email Settings. However, I found out there has been a defect entered on this problem since October of last year. So I am looking for a work around. The official work around from Quest is to only have one email address as the category CC.  For us, the problem is that each category as two or three email addresses set for notifications. So I need to try to write a custom rule to get around these issues and make the system work the way we need it. The SQL appears to only allow for one email address in the NEWTICKETEMAIL column. I do not want to have to create up to 16 email distribution lists with two or three people on it and the same number of rules for each distribution list. I need the notification to go to specific people (more than one person and not the entire office distribution list). Is there a way to have the CC_LIST column on the HD_TICKET.CATEGORY table used for the sent to email in a custom rule or can we put more than one email in the NEWTICKETEMAIL column? If so, how? Will commas or semi colons work?

Note: I have already searched this forum and the only things I have found were from 2012, so they may not longer apply to the system. I can't imagine that no one else has ran into this issue since the defect was entered. Is there anything that anyone can provide that may help?

0 Comments   [ + ] Show comments


All Answers


The NEWTICKETEMAIL column can include multiple email addresses. Comma separated should work.

Answered 03/18/2019 by: chucksteel
Red Belt

  • When I do that, it only sends an email to the first email address. I have tried this with a comma, a comma and a space, a semi-colon, and a semi-colon with a space after it. It will only send the email to the first email address in the list.
    • And you're setting it in the SQL Select statement, correct?
      • I did. Here is the SQL that I am using, if you do not mind looking it over to make sure I didn't miss something.
        -- ticket fields
        HD_TICKET.ID, -- $id
        HD_TICKET.ID AS TICKNUM, -- $ticknum
        HD_TICKET.TITLE, -- $title
        HD_TICKET.CUSTOM_FIELD_VALUE0 AS custom_1, -- $custom_1
        HD_TICKET.CUSTOM_FIELD_VALUE1 AS custom_2, -- $custom_2
        HD_TICKET.CUSTOM_FIELD_VALUE3 AS custom_4, -- $custom_4
        HD_TICKET.CUSTOM_FIELD_VALUE4 AS custom_5, -- $custom_5
        DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
        DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
        -- change fields
        C.COMMENT, -- $comment
        C.DESCRIPTION, -- $description
        GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
        ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
        -- about the updater
        UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
        UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
        UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
        -- about the owner
        OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
        OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
        OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
        IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
        -- about the submitter
        SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
        SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
        SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
        -- about priority
        P.NAME AS PRIORITY, -- $priority
        -- about status
        S.NAME AS STATUS, -- $status
        -- about impact
        I.NAME AS IMPACT, -- $impact
        -- about category
        CAT.NAME AS CATEGORY, -- $category
        -- other fields
        CAT.CC_LIST AS CATCC, -- $catcclist
        -- -- example of static distribution list
        'email1,email2' AS NEWTICKETEMAIL -- $newticketemail
        /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID=<CHANGE_ID>
        /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
        /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
        /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
        /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
        /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
        /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
        /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
        /* this is necessary when using group by functions */
        HAVING 1=1
      • And you are using the Email each recipient in query results option?
      • Yes. I have the box next to "Email each recipient in query results" checked. In the field "Column containing email addresses:" I have entered "NEWTICKETEMAIL". It is set to run on Ticket Save. I also tried $newticketemail to see if that made a difference.
        This is the Last Run Log: 03/18/2019 07:59:20> Starting: 03/18/2019 07:59:20 03/18/2019 07:59:20> Executing Select Query... 03/18/2019 07:59:20> selected 1 rows 03/18/2019 07:59:20> Sending email... 03/18/2019 07:59:20> sent mail to email1 03/18/2019 07:59:20> Sending ticket notifications... 03/18/2019 07:59:20> sent mail to 1 of 1 03/18/2019 07:59:20> Ending: 03/18/2019 07:59:20
      • Has anyone gotten it to work with multiple email addresses in the NEWTICKETEMAIL column?
        I found https://www.itninja.com/question/ticketing-queue-custom-e-mail but it doesn't address multiple email addresses.

What can I change on this SQL Code to where when I add comment it will automatically go to "'****@****.****' AS NEWTICKETEMAIL -- $newticketemail"?

Answered 04/01/2019 by: SoheebH
White Belt

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