Help with SQL- Send e-mail to certain distribution group(s) depending on what words are in the ticket summary

So for example...

if we can some how use something like:

IF HD_TICKET.SUMMARY like '%Company: Company1%' then  'Emailgroup1@company.com' AS NEWTICKETEMAIL -- $newticketemail

IF HD_TICKET.SUMMARY like '%Company: Company2%' then  'Emailgroup2@company.com' AS NEWTICKETEMAIL -- $newticketemail

IF HD_TICKET.SUMMARY like '%Company: Company3%' then  'Emailgroup3@company.com' AS NEWTICKETEMAIL -- $newticketemail

IF HD_TICKET.SUMMARY like '%Company: Company4%' then  'Emailgroup4@company.com' AS NEWTICKETEMAIL -- $newticketemail

IF HD_TICKET.SUMMARY like '%Company: Company5%' then  'Emailgroup5@company.com' AS NEWTICKETEMAIL -- $newticketemail

Then send the E-mail out when the ticket is closed to the NEWTICKEREMAIL variable etc..

Anyone have knowledge if this is possible?

Thanks in advance for any assistance


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: Hobbsy 2 years ago
Red Belt

Top Answer

First off if you want to send an email to a distribution group from a ticket rule, you will need to have a single email address for that distribution group, as you can only send an email to a single address.

That being said you could set up a CASE statement, which basically would give you an output value based on the text in the ticket.

If that output value was the correct email address for the group then that would probably work.

If not I would hide all the mailing list emails individually, as default values in hidden custom fields so that all are available as data values in the select statement, then run the CASE in the update statement to set which custom field you want to use as the email address.

feel free to reach out if you would like to discuss and explore further

  • Hey, thanks for the idea.. this is what I ended up doing.. and the E-mail address is a single address that is tied to a distribution group in AD, so to adjust we just need to adjust who is in what groups to get the appropriate e-mails :)

    I do have another ? if you can shed some light...

    Is there a way in the select statement to create a variable that holds the resolution text from this tickets child ticket that has user-accounts in the title?? and if so, can the variable then be used in the body of the E-mail that gets sent out when this rule processes? - jct134 2 years ago
Posted by: JordanNolan 2 years ago
10th Degree Black Belt

Instead of trying to maintain this all in one rule that is going to have to be updated every time a new company comes or goes, I have made use of individual ticket rules for each situation.  In my case I setup the rule using the Rule Wizard so it look for:

  • Submitter Like @Companyxyz.com
  • Change assigned to Bob@Companyxyz.com

 If you have LDAP integration to pull in user information you can pull in attributes you maintain in AD like Location, or Company Name and map them to the KACE user attributes and base rules off of those.

  • our submitters for this process do not come from any person.. thanks for the thought though - jct134 2 years ago

Don't be a Stranger!

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

Sign up! or login

View more:


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