/build/static/layout/Breadcrumb_cap_w.png
09/19/2018 122 views
Hi,

I need some help with sql  I have a custom rule that that is working and need just a tweak. The rule sends an email to an external group when the ticket is created and specific category is selected. But if they wrong category is selected and we change it, it does not send the email. So looking at the sql I see it is looking for ticket created and category is but I dont know how to go after a change.

SELECT

HD_TICKET.ID,

HD_TICKET.ID AS TICKNUM,

HD_TICKET.TITLE,

HD_TICKET.CUSTOM_FIELD_VALUE0 AS EMPLOYEE_NAME,

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

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

C.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,

UPDATER.USER_NAME AS UPDATER_UNAME,

UPDATER.FULL_NAME AS UPDATER_FNAME,

UPDATER.EMAIL AS UPDATER_EMAIL,

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

OWNER.USER_NAME AS OWNER_UNAME,

OWNER.FULL_NAME AS OWNER_FNAME,

OWNER.EMAIL AS OWNER_EMAIL,

IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER,

SUBMITTER.USER_NAME AS SUBMITTER_UNAME,

SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,

SUBMITTER.EMAIL AS SUBMITTER_EMAIL,

SUBMITTER.WORK_PHONE AS SUBMITTER_PHONE,

P.NAME AS PRIORITY,

S.NAME AS STATUS,

I.NAME AS IMPACT,

CAT.NAME AS CATEGORY,

'externalhelpdesk@company.com' 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%' AND

(

CAT.NAME LIKE '%Upgrades/Project Rollouts%'

)

GROUP BY HD_TICKET.ID

HAVING 1=1

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
The best option is to create a second rule that matches ticket changes. Instead of looking for C.DESCRIPTION like 'TICKET CREATED%', look for C.DESCRIPTION LIKE '%Category%to "Upgrades/Project Rollouts"%'


Answered 09/20/2018 by: chucksteel
Red Belt

  • Chuck Thank you! This works perfectly. I didnt think of that of doing it with 2nd rule.