Edit SQL of ticket rule to not only e-mail when new ticket is created, but to also NOT e-mail if the ticket is a Child ticket
Hello,
We have a ticket rule that triggers an E-mail to the whole IT department upon ticket creation...
We want to start using the ability of creating parent\child tickets etc..
But we do not want e-mails sent if the child or parent ticket (newly created) are assigned to someone (or just not e-mailed out at all for these parent or child tickets)
I think our ticket rule sees the child\parent (newly created) ticket as a "new created" ticket and then this ticket rule sends the e-mail out to the department..
If anyone with knowledge of SQL can show how this can be edited to exclude if the ticket is parent or child.. that would be awesome
HTnaks
Here is what we use in the ticket rule today..
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/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
HD_TICKET.CUSTOM_FIELD_VALUE2 AS LOCATION, -- $location
-- -- example of static distribution list
'ITDept@xxxxxxxxxx.com' 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 '%CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Answers (1)
Top Answer
To exclude child tickets add this line after C.DESCRIPTION LIKE '%CREATED%'
and HD_TICKET.PARENT_ID != 0
When a ticket is assigned a parent, it will have the ticket ID of the parent in that field, otherwise it will be a zero.
Comments:
-
Hey, thanks for the info.. much appreciated
I was wondering what could be added to only send an e-mail if the ticket is not assigned to an owner yet, so if we create a child ticket and do not assign it to a tech it will e-mail out, but if we assign it to a tech during the creation then it does not e-mail...
Thanks in advance for any thoughts..
Jason - jct134 5 years ago-
That depends on who you want to receive the email. - chucksteel 5 years ago
-
our code above sends e-mail to a group so everyone in IT gets the E-mail, so my thought was.. if it is/was a normal "created" ticket the e-mail goes to our group e-mail like it does today, but if the ticket is assigned to an owner during the process (created a child for example) then it will not e-mail out.. I just do not know what the code would be, or should be..
"and OWNER.USER_NAME = Unassigned"
Something like that?
Jason - jct134 5 years ago -
HD_TICKET.OWNER_ID = 0 is the easiest way to detect an unassigned ticket. - chucksteel 5 years ago
-
So If I added after the
C.DESCRIPTION LIKE '%CREATED%' section
"and HD_TICKET.PARENT_ID != 0" (if parent exists then this would NOT be 0) so should it be and HD_TICKET.PARENT_ID = 0 to NOT send e-mail if ticket is a child? and if so, if we also add
"and HD_TICKET.OWNER_ID = 0" (if owner is "unassigned" by default, would this ID be 0?)
So my code should be
C.DESCRIPTION LIKE '%CREATED%'
and HD_TICKET.PARENT_ID = 0
and HD_TICKET.OWNER_ID = 0
This will E-mail if ticket is "created"
and "parent_ID=0"
and "owner_ID=0"
Hope that makes sense..?
Thanks
Jason - jct134 5 years ago -
That is correct. With that statement this rule will only email IT when tickets are created that do not have a parent and are not assigned to anyone. - chucksteel 5 years ago
-
I really appreciate your assistance..
When I have this as my code
C.DESCRIPTION LIKE '%CREATED%'
and HD_TICKET.PARENT_ID = 0
and HD_TICKET.OWNER_ID = 0
it seems that if I create the child ticket (save and create child) and do not assign the ticket to anyone (Unassigned) no E-mail is sent... and of course if I create the child ticket (save and create child) and assign it to someone no e-mail is also sent which is what I want.. just not sure why if it is unassigned it does not E-mail as well...
Any suggestions?
Jason - jct134 5 years ago -
In both of your test cases the ticket will have a parent, so PARENT_ID will not be zero and the rule will not run. - chucksteel 5 years ago
-
OK, so my thinking is wrong then that "all" 3 criteria would have to be met in order for the E-mail to be sent.. so would this work...
C.DESCRIPTION LIKE '%CREATED%'
and HD_TICKET.PARENT_ID = 0
or HD_TICKET.OWNER_ID = 0
so, any ticket created that DOES NOT have owner E-mail will be sent, as well as any created ticket without a parent?
Would that work do you think they way I am thinking??
I want any newly created ticket that is without owner to get E-mail as well as any newly created ticket that even if it has a parent but also has no assigned user to have E-mail sent..
So maybe the just
C.DESCRIPTION LIKE '%CREATED%'
and HD_TICKET.OWNER_ID = 0
is all I need?? as regardless of parent or not, if the ticket is created & has no owner it will send E-mail...
I think that sounds right to me??
What do you think?
Thanks again for all your assistance..
Jason - jct134 5 years ago -
Yes, you are correct. - chucksteel 5 years ago
