Custom Email Rule Assistance
All,
I am new to SQL and trying to get the Rule working to send an email to a distribution list (or any email address) when a user creates a new ticket via email. I have read many different questions and answers on here and can't seem to get it to work. Below is the SQL I am using: Please Help!
Here is my current SQL Query:
SELECT
-- ticket fields
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
-- change fields
C.COMMENT, -- $comment
-- about the submitter
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- example of static distribution list
'test@testemail.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 'TICKET CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
My email is as follows:
$submitter_fname has opened a ticket.
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment
If I email the results of the query, the query works and sends an email to the desired email address with the information from the query. If I do NOT email the results of the query, No emails are sent.
After trying to test the query by creating a new ticket via email I get these results when looking at the ticket Rule:
12/03/2014 16:11:48> Starting: 12/03/2014 16:11:48 12/03/2014 16:11:48> Executing Select Query... 12/03/2014 16:11:48> selected 1 rows 12/03/2014 16:11:48> Sending email... 12/03/2014 16:11:48> sent mail to test@testemail.com 12/03/2014 16:11:48> Sending ticket notifications... 12/03/2014 16:11:48> sent mail to 0 of 1 12/03/2014 16:11:48> Ending: 12/03/2014 16:11:48
Does anyone know why I get the highlighted section saying "Sent Mail to 0 of 1?"
I have even followed the kace article below (which seemed like it would be pretty simple)
I get the same results.
https://support.software.dell.com/k1000-systems-management-appliance/kb/111222
Any thoughts are appreciated!
P.S. I have changed the test@testemail.com to an actual email address.
Thanks,
Mike
2 Comments
[ + ] Show comments
-
Do you have NEWTICKETEMAIL in the "Column containing email addresses" field? - h2opolo25 9 years ago
-
Thanks for the assistance! I had $NEWTICKETEMAIL in the column containing email addresses. It is working great now! Thanks again. - mhamel 9 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
StockTrader
9 years ago
Hello,
your rule works for me really well.
Check the rule configuration!
i configured it in this way.
Kind regards,
Marco - StockTrader
