Custom Rule to send email notifications to either the Category_CC with multiple emails or a static email list
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?
Answers (4)
The NEWTICKETEMAIL column can include multiple email addresses. Comma separated should work.
Comments:
-
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. - jessburd 5 years ago
-
And you're setting it in the SQL Select statement, correct? - chucksteel 5 years ago
-
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.
SELECT
-- 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
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- 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
FROM HD_TICKET
/* 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
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.DESCRIPTION LIKE '%CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1 - jessburd 5 years ago -
And you are using the Email each recipient in query results option? - chucksteel 5 years ago
-
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 - jessburd 5 years ago -
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. - jessburd 5 years ago
Although the bug has been resolved, and this isn't exactly what was being looked for, we have some ticket rules that email all the users from a specific label.
CAT.NAME AS CATEGORY, -- $category
-- group email addr Comma Separated
(SELECT GROUP_CONCAT(USER.EMAIL SEPARATOR ', ') AS ADDRESSLIST
FROM
LABEL
INNER JOIN USER_LABEL_JT ON USER_LABEL_JT.LABEL_ID = LABEL.ID
INNER JOIN USER ON USER.ID = USER_LABEL_JT.USER_ID
WHERE
/*LABEL.NAME = 'GRP_ACCESS_BADGE_ADMINS') AS GROUPMAIL*/
LABEL.ID = '1234') AS GROUPMAIL -- ID = Users: Investigations FLEX
FROM HD_TICKET
Back on topic, from the in-page help:
This rule assumes that the Select SQL query will return a column that will contain an email address, and that you want to send a mail to that address. Basically, for each resulting row from the "Select SQL" statement, the text in the "Email Body" will be sent to the recipients from the field "Email Column".
So, for each ticket, whatever column you put in the "Column containing email addresses" field will have one email sent per ticket. If the column/row contains a list of comma separated values (like the ticket CC), then all the people in that column/row will receive a copy of the email.
In the query posted above, junk this line:
'email1,email2' AS NEWTICKETEMAIL -- $newticketemail
and change your "Column containing email addresses" box to "CATCC". This will send the email to everyone in the ticket CC list.
Now, the list is supposed to be CSV, and it shows as CSV in KACE, but if you access the database, the commas are replaced with semicolons on the backend. When you send an email to multiple people, you have to use semicolons instead of commas. So it should work.
What can I change on this SQL Code to where when I add comment it will automatically go to "'****@****.****' AS NEWTICKETEMAIL -- $newticketemail"?
Comments:
-
I'm probably a bit late here, but just replace the 'email1,email2' AS NEWTICKETEMAIL -- $newticketemail with a single email address. Just make sure NEWTICKET email is capitalized in the email box or KACE will not recognize it (and it doesn't really tell you either). - JamesinIT 3 years ago
I have this working where I bring together a few email addresses:
I add in my user's manager when we close some tickets so I add these lines under the "Submitter" portion of the query. I then use the CONCAT_WS function to add the user's email and manager's email, and seperate them with a comma:
MANAGER.ID AS MANAGER_ID, -- $manager_id
MANAGER.FULL_NAME AS MANAGER_NAME, -- $manager_name
MANAGER.EMAIL AS MANAGER_EMAIL, -- $manager_email
CONCAT_WS(" ,", SUBMITTER.EMAIL, MANAGER.EMAIL) AS NOTIFY, -- $notify
Also in order for the manager to work, I add this down in the join portion of the query (this also assumes you import the users manager):
/* manager *********/ LEFT JOIN USER MANAGER ON MANAGER.ID = SUBMITTER.MANAGER_ID
Finally, when I send the email, I use the NOTIFY value from the CONCAT function. Just remember - this has to be UPPERCASE in the "Column containing Email Address" box, but all other fields need to be lowercase and in the format of $notify (or whatever the variable was you used).
