/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 email a group, when status changes

01/17/2019 297 views

Need SQL to have an email sent to a group when two specific Status are used.  Closed Back in Service and Sent to wireless.


Thank you. 


0 Comments   [ + ] Show comments

Comments


All Answers

0

I got it.


SELECT


      -- ticket fields


      HD_TICKET.ID, -- $id


      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


      -- change fields


      C.COMMENT, -- $comment


      C.DESCRIPTION, -- $description


      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',


       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000.camdenpd.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')


       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


      -- about the updater


      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname


      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname


      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email


      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional


      -- about the owner


      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname


      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname


      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email


      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user


      -- 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


      -- about priority


      P.NAME AS PRIORITY, -- $priority


      -- about status


      S.NAME AS STATUS,   -- $status


      -- about impact


      I.NAME AS IMPACT,   -- $impact


      -- about category


      CAT.NAME AS CATEGORY, -- $category


      -- other fields


      -- -- example of static distribution list


      'Fleet_Kace@youremail' AS NEWTICKETEMAIL -- $newticketemail


    FROM (HD_TICKET, HD_STATUS)


     /* 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

    HD_STATUS.NAME like '%Closed Back in Service%'


    /* C.DESCRIPTION LIKE '%CREATED%'


      * this is necessary when using group by functions */


    GROUP BY HD_TICKET.ID


Answered 01/18/2019 by: lockej
White 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

View more:

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