/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Email Rule Assistance

12/03/2014 1207 views
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:
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.

All 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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