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


Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Do you have NEWTICKETEMAIL in the "Column containing email addresses" field?
  • Thanks for the assistance! I had $NEWTICKETEMAIL in the column containing email addresses. It is working great now! Thanks again.
Please log in to comment

Answers

2
Hello,

your rule works for me really well.
Check the rule configuration!
i configured it in this way.



Kind regards,
Marco - StockTrader
Answered 12/05/2014 by: StockTrader
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share