Need help with parent-child ticket rule (notify child ticket owners when parent ticket approval set)
Hoping someone can help, I'm pretty much at a loss with this one....
I'm trying to write a custom ticket rule that will email the owners of child tickets when the corresponding parent ticket is initially approved and saved (it's just a placeholder that doesn't get updates beyond this). Everything works as expected in the MySQL Query Browser and when I use "Run Now" in the ticket rule's Edit Detail screen. However, if the ticket rule runs as scheduled (or on Ticket Save, which is the goal), it just says "Executing Select Query..." in the Run Log and has the following entry in the K1000 Server Logs:
[Tue Jun 12 16:25:56 2012] [error] [client 192.168.11.52] PHP Warning: sprintf() [<a href='function.sprintf'>function.sprintf</a>]: Too few arguments at /kbox/kboxwww/include/KLocale.class.php(855) : eval()'d code:10, referer: http://kbox/adminui/ticket_rule.php?ID=44
I researched "Too few arguments" and My SQL and landed in a mess of potential bugs, so I'm at a loss. Any suggestions (or fixes) would be very greatly appreciated.
Additional notes - I've already tried rebooting the KBOX in case something was stuck. Amusingly, when this rule is setup in the Child queue (queue 1), it runs fine when a ticket is updated. Unfortunately, setup like that it doesn't catch the Parent queue (queue 2) ticket being saved. So this has to be run from the Parent queue in order to catch the ticket save - which it *is* doing (based on the Starting time in the Run Log), it just doesn't complete the run.
* Update and further thoughts in my answer below.
Thanks very much!!!
John
_____________________________
Original code - tweaked in answer below and executing now, but still having an issue.
select USER.EMAIL as OWNER_EMAIL, PARENT.ID AS ID, PARENT.TITLE as ISSUE, PARENT.APPROVAL as APPROVAL,
PARENT.CUSTOM_FIELD_VALUE0 as LOCATION, PARENT.CUSTOM_FIELD_VALUE2 as FULLNAME, PARENT.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
PARENT.CUSTOM_FIELD_VALUE4 as JOBTITLE, PARENT.CUSTOM_FIELD_VALUE5 as MANAGER, PARENT.CUSTOM_FIELD_VALUE6 as DATEOFHIRE,
PARENT.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(PARENT.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL
from HD_TICKET PARENT, HD_TICKET CHILD
LEFT JOIN USER on USER.ID = CHILD.OWNER_ID
where PARENT.ID = CHILD.PARENT_ID
and PARENT.APPROVAL != ''
Answers (3)
Reference the child tickets via a join statement. Thanks again, Chuck! Working Select Query: select USER.EMAIL as OWNER_EMAIL, HD_TICKET.ID AS ID, HD_TICKET.TITLE as ISSUE, HD_TICKET.APPROVAL as APPROVAL, HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL from HD_TICKET JOIN HD_TICKET CHILD on CHILD.PARENT_ID=HD_TICKET.ID LEFT JOIN USER on USER.ID = CHILD.OWNER_ID where (HD_TICKET.APPROVAL != '' and CHILD.HD_QUEUE_ID = 1 and HD_TICKET.HD_QUEUE_ID = 2)
I think that if you reference the child tickets via a join you might have better luck. So instead of from HD_TICKET PARENT, HD_TICKET, just have from HD_TICKET join HD_TICKET CHILD on CHILD.PARENT_ID = HD_TICKET.ID, or something like that.
Comments:
-
Chuck - Thanks for the idea, that got it working!!! Full documentation below.
John
______________________________________
Title:
Notification On User Approval (create in Queue 2 - HR Queue)
Order:
20
Notes:
Sends an email to (Queue 1 - IT Helpdesk) child ticket owners when an (Queue 2) HR ticket's approval has been specified.
Frequency:
on Ticket Save
_____________________________________
Select Query:
select USER.EMAIL as OWNER_EMAIL, HD_TICKET.ID AS ID, HD_TICKET.TITLE as ISSUE, HD_TICKET.APPROVAL as APPROVAL,
HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE,
HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL
from HD_TICKET
JOIN HD_TICKET CHILD on CHILD.PARENT_ID=HD_TICKET.ID
LEFT JOIN USER on USER.ID = CHILD.OWNER_ID
where (HD_TICKET.APPROVAL != '' and CHILD.HD_QUEUE_ID = 1 and HD_TICKET.HD_QUEUE_ID = 2)
__________________________________________
X Send an email for each result row
Subject:
[TICK:$id] USER CHANGE APPROVAL: $approval
Email Column:
OWNER_EMAIL
Email Body:
A ticket's approval status in the HR Helpdesk queue has been updated, please review.
Ticket ID: $id
Created: $created
Issue: $issue
Category: $category
Remote Access (VPN/Citrix): $remote
Approval: $approval
User: $fullname
Date of Hire: $dateofhire
Job Title: $jobtitle
Department: $department
Location: $location
Manager: $manager
___________________________________________________________________
Thanks,
Company IT - jverbosk 11 years ago -
I'm glad that worked, John. I have always been more comfortable with joins instead of selecting from multiple tables. - chucksteel 11 years ago
-
Thanks for the tip, Chuck!
I've still got a lot to learn in regards to MySQL queries. This was my attempt at cobbling together a parent-child ticket rule (that GillySpy and dchristian helped me with previously) with wizard-generated code, and although I've figured out a little more than I knew at that time, I'm still pretty green in regards to parent-child references. Hopefully I have enough now to do anything I need with the parent-child queries, but if not I know who to pester now. ^_^
John - jverbosk 11 years ago
Could the problem be with one of the other parts of the rule, and not the SQL? Perhaps there's a value in the email body field causing the issue?
Comments:
-
Thanks, but I tested this during troubleshooting and it's the same when I use the "Send query results to someone" option and just specify my email address. Run manually via "Run Now" it goes fine, but running "on Ticket Save" or at the scheduled time generates the error.
John - jverbosk 11 years ago
OK, I got the rule to execute completely with the following modification (removed the CHILD aliases). The rule still runs properly when not specifying the queue numbers in the last line, I just included it in case anyone else had that idea. Even with the rule executing properly, the problem is basically the same (and possibly a catch 22...).
1) When the rule is setup in queue 1 (the Child queue) to run on Ticket Save and a child ticket is saved, the rule runs properly. However, this is not the desired setup as the rule needs to catch the parent ticket being saved and I'm not sure if there's a way to code for this if the ticket rule is setup in the child queue.
2) When the rule is setup in queue 2 (the Parent queue) to run on Ticket Save and a parent ticket is saved, the rule executes, but results in "selected 0 rows" - in other words, no matches. However, if I use the "Run Now", the rule pulls up all of the matches (which are present, as confirmed by the MySQL Query Browser and running the ticket rule manually).
At this point I suspect the catch 22 part may be that although the rule is executing in queue 2 (the Parent queue), it's not able to reference the child tickets properly because it ultimately needs an HD_TICKET.CHILD_ID table in order to work? Then again, it does work manually.... again, I'm at a loss here, so any ideas would be greatly appreciated.
John
_______________________________
select USER.EMAIL as OWNER_EMAIL, PARENT.ID AS ID, PARENT.TITLE as ISSUE, PARENT.APPROVAL as APPROVAL,
PARENT.CUSTOM_FIELD_VALUE0 as LOCATION, PARENT.CUSTOM_FIELD_VALUE2 as FULLNAME, PARENT.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
PARENT.CUSTOM_FIELD_VALUE4 as JOBTITLE, PARENT.CUSTOM_FIELD_VALUE5 as MANAGER, PARENT.CUSTOM_FIELD_VALUE6 as DATEOFHIRE,
PARENT.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(PARENT.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL
from HD_TICKET PARENT, HD_TICKET
LEFT JOIN USER on USER.ID = HD_TICKET.OWNER_ID
where PARENT.ID = HD_TICKET.PARENT_ID
and (PARENT.APPROVAL != '' and HD_TICKET.HD_QUEUE_ID = 1 and PARENT.HD_QUEUE_ID = 2)