KACE Product Support

I think I've narrowed this down a bit. When setting the submitter_email as the column --

SUBJECT:[TICK:$ticknum] $title
EMAIL COLUMN:SUBMITTER_EMAIL

It works.

However, when setting it as NEWTICKETEMAIL --
SUBJECT:[TICK:$ticknum] NEW TICKET: $title
EMAIL COLUMN:NEWTICKETEMAIL

That variable does not work.

This is a snippet from the SQL code --

-- other fields
-- -- example of static distribution list
'OfficeSupport@mycompanyname.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET

Obviously, I've changed the "mycompanyname.com to my distribution list. When the field 'column containing email addresses:' is set to SUBMITTER_EMAIL, it will send the notification to the submitter (obviously not what I want).

When it is set to NEWTICKETEMAIL, nothing happens. - adrianK 7 years ago

Here is the entire code minus the private info. Options selected:
* Enabled
* Email each recipient in query results
* Frequency - on Ticket Save

That's it.

SQL ---

SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
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',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://helpdesk.mycompanyname.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\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
-- -- example of static distribution list
'OfficeSupport@mycompanyname.com' 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 - adrianK 7 years ago

Yeah, all of that should work. Try turning on the option to email the results. This will send you an email with a table showing the query results and helps for debugging. - chucksteel 7 years ago

OK, so the SQL all appears correct. The e-mail result returns an e-mail including the NEWTICKETEMAIL field with the correct e-mail address --

---
Here's the e-mail I get. Sorry about the formatting but it appears to be working, just not sending an e-mail to my distribution list which I know for sure works
---

Newticketemail
OfficeSupport@mycompany.com

New Office Support Ticket
New Rule
# Id Ticknum Title Created Modified Comment Description History Updater Uname Updater Fname Updater Email Updater Conditional Owner Uname Owner Fname Owner Email Owner User Submitter Uname Submitter Fname Submitter Email Priority Status Impact Category Newticketemail
1 23698 23698 Test 2 3 4 Oct 05 2017 11:11:32 AM Oct 05 2017 11:11:32 AM Hello, please send the debugging e-mail if it works. Ticket Created Added attachment "winmail.dat" ----- Change by me@mycompany.com at 2017-10-05 11:11:32 ----- Ticket Created Added attachment "winmail.dat" Hello, please send the debugging e-mail if it works. Please see your ticket at http://helpdesk.mycompany.com/userui/ticket.php?ID=23698
amartin Adrian Martin me@mycompany.com Adrian Martin Unassigned amartin Adrian Martin me@mycompany.com Medium- It can wait 24hrs New Affects work productivity for team Desk issue OfficeSupport@mycompany.com.com - adrianK 7 years ago