I would like to have an e-mail notification for new tickets sent to both the submitter and a static e-mail address.

I have successfully been able to do this with the code that follows so that the same notification e-mail is sent to both addresses, however I would like both addresses to appear in the same e-mail (i.e. as an e-mail with 2 addresses, rather than 2 e-mails with one address each).

Does anyone have any suggestions?


With the below select statement, the field MULTIEMAIL is entered in the column containing e-mail addresses field


SELECT

      -- ticket fields

      HD_TICKET.ID, -- $id

      HD_TICKET.SUMMARY, -- $summary

      HD_TICKET.ID AS TICKNUM, -- $ticknum

      HD_TICKET.TITLE, -- $title

      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created

      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified

  
      -- about the submitter

      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname

      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname

SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email

   
 
   
      -- other fields

      -- -- example of static distribution list


CONCAT(SUBMITTER.EMAIL,", ","ithelp@company.com") AS MULTIEMAIL   -- $multiemail

    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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
The challenge you have is that there is no cc field available to populate within a ticket rule, which means, in short you have no where to post the data, even if you select it within the select statement.

The best we have achieved is exactly as you describe, a comma separated list, used to send the same email multiple times.
Answered 07/22/2015 by: Hobbsy
Red Belt

  • Ok - thanks
    • How do I mark a question as "answered"?
Please log in to comment
Answer this question or Comment on this question for clarity