/build/static/layout/Breadcrumb_cap_w.png

SQL help needed

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


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
2

Top Answer

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"%'



Comments:
  • Chuck Thank you! This works perfectly. I didnt think of that of doing it with 2nd rule. - bozadmin 5 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

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