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
Jason
Answers (2)
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
Comments:
-
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
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.
Comments:
-
our submitters for this process do not come from any person.. thanks for the thought though - jct134 2 years ago
