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 != ''

 

Answer Summary:
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)
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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.

Answered 06/14/2012 by: chucksteel
Red Belt

  • 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
  • I'm glad that worked, John. I have always been more comfortable with joins instead of selecting from multiple tables.
  • 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
Please log in to comment

Answers

0

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?

Answered 06/13/2012 by: chucksteel
Red Belt

  • 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
Please log in to comment
0

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)

Answered 06/13/2012 by: jverbosk
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity