SQL Help - Send Email to Different Distribution Lists Based on Custom_1 Input (K1200)
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
Answers (4)
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
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
That works! Thank you very much!
Cheers,
Steve
Comments:
-
Keter can you upload your code so I can see where you put the case statement? - mopalach 11 years ago
-
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 - Keter 11 years ago
-
Nice Thanks - mopalach 11 years ago