I have setup a custom ticket rule as per http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk, which works wonderfully for sending email to just the one distribution list on ticket creation.  I would like help modifying the query to send to different distribution lists based on values stored in the Custom_1 field.  We use Custom_1 for site lists, and each site has a specific distribution list associated with it.

So, for example, if a new ticket is created and the Custom_1 option "Central Office" is selected, an email is generated and sent to the "co.helpdesk@company.com".  If the Custom_1 option "Annex" is selected instead, an email is generated and sent to "annex.helpdesk@company.com".

I've tried for several days now to get it to work, but I'm at a loss.  I'm no SQL expert, but if someone could point me in the right direction it would be greatly appreciated.


Cheers,
Steve

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Can you post what SQL you have now, if any?

Answered 04/25/2012 by: chucksteel
Red Belt

Please log in to comment
1

Sure, but it is mostly exactly like the query in the URL above: (with addresses removed)

---------------------------------------------------------------------------------------------------------

SELECT

      -- ticket fields

      HD_TICKET.ID, -- $id

      HD_TICKET.ID AS TICKNUM, -- $ticknum

      HD_TICKET.CUSTOM_FIELD_VALUE0 as SITE, -- $site

      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://kbox.***.***.***/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


      'helpdesk@****.texas.gov' 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

 

---------------------------------------------------------------------------------------------------------

So, I am pulling in the site code (HD_TICKET.CUSTOM_FIELD_VALUE0 as SITE), and this does work - when I enable the sending of query results to myself I see the correct site code in HD_TICKET.CUSTOM_FIELD_VALUE0.

I cannot figure out how to change this query so that I have different NEWTICKETEMAIL values based on what is in HD_TICKET.CUSTOM_FIELD_VALUE0 (my example above lists 2 example sites with their associated email addresses).  I think my problem has been that that this entire query is inside a SELECT statement already - as I said I'm no SQL expert, but this kinda makes sense to me.

Cheers,
Steve

Answered 04/25/2012 by: Keter
Senior Yellow Belt

Please log in to comment
1

You want to use the MySQL CASE function:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

I haven't used it personally but I believe the syntax in this instance would be:

 case CUSTOM_FIELD_VALUE0 
when "Central Office" then co@helpdesk.company.com
when  "Annex" then annex.helpdesk@company.com
end as NEWTICKETEMAIL
Answered 04/26/2012 by: chucksteel
Red Belt

Please log in to comment
0

That works!  Thank you very much!

 

Cheers,
Steve

Answered 05/01/2012 by: Keter
Senior Yellow Belt

  • Keter can you upload your code so I can see where you put the case statement?
    • Sorry it took me so long to upload this, Mopalach. I've simplified this a tad - I have many, many cases...



      SELECT

      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.CUSTOM_FIELD_VALUE0 as SITE, -- $site
      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

      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://kbox.****.****.***/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
      ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

      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

      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

      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email

      P.NAME AS PRIORITY, -- $priority
      S.NAME AS STATUS, -- $status
      I.NAME AS IMPACT, -- $impact
      CAT.NAME AS CATEGORY, -- $category

      case CUSTOM_FIELD_VALUE0

      when "Agency-Wide" then 'helpdesk@****.*****.***'
      when "Remote Site 1" then 'site1.support@****.*****.***'
      when "Central Office" then 'helpdesk@****.*****.***'
      when "Annex" then 'helpdesk@****.*****.***'
      when "Remote Site 2" then 'site2.support@****.*****.***'

      end as NEWTICKETEMAIL

      FROM HD_TICKET

      JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
      AND C.ID=<CHANGE_ID>
      JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
      JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
      JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
      JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
      JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
      LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
      LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
      LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

      WHERE

      C.DESCRIPTION LIKE 'TICKET CREATED%'

      GROUP BY HD_TICKET.ID

      HAVING 1=1
  • Nice Thanks
Please log in to comment
Answer this question or Comment on this question for clarity

Share