Hi,

I'd like to ask all you good folks on how would you create a ticket rule for the following:

1. Automatically close the parent ticket after all the child tickets has been closed.

2. To cancel an already triggered process like the New Hire process, which will close the parent ticket and the child tickets without actually going through all the entire process. Or just cancel the entire process without opening the rest of the child tickets.

If any of you can help me on this, I'd really appreciate it.

Thanks in advance.

1 Comment   [ + ] Show Comment

Comments

  • So I am confused on this. Have read the blog, etc. and not sure which script I am supposed to enter.

    Could I get some assistance? I only need to run this on one queue - I have copied what I thought was the working script and update query, but it is not closing the Parent. (I have it copied and running in the Parent Ticket portion).
Please log in to comment

Community Chosen Answer

2

OK, this has been tested *very* thoroughly (SELECT & UPDATE queries) and works as requested ACROSS ALL QUEUES.  I tweaked the UPDATE query so it applies the relative STATUS.ID value of the child tickets to the parent tickets, which prevents the issue of having a different queue's STATUS.ID value assigned if the parent ticket is in a different queue than the child tickets.  For example in my setup, the STATUS.ID for "closed" in queue 1 (where my child tickets are) is 2, but in queue 2 (where my parent tickets are) it's 12 - the UPDATE query pulls the STATUS.ID value from the child tickets and applies the queue-appropriate STATUS.ID value to the parent tickets.

I also added a final line to the SELECT query (AND HD_STATUS.STATE not rlike 'closed') to filter out parent tickets that have already been closed. 

The UPDATE query was a bit tricky to figure out, but it works.  You can set it to run on ticket save or run it manually/scheduled.  I'll write a blog to explain it a bit more later.

John

___________________________

Ticket Rule to Close Parent Ticket When All Child Tickets Closed

*Select Query*
Lists all open parent tickets when all child tickets have closed status

SELECT HD_TICKET.ID, HD_STATUS.NAME, HD_STATUS.STATE, FILTER.CSTATE
FROM HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT HD_TICKET.ID, GROUP_CONCAT(CHILD.STATE) AS CSTATE
  FROM HD_TICKET
  JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
  JOIN
   (SELECT HD_TICKET.ID, HD_STATUS.STATE, HD_TICKET.PARENT_ID
    FROM HD_TICKET
    JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    WHERE HD_TICKET.PARENT_ID != 0)
  CHILD ON (CHILD.PARENT_ID = HD_TICKET.ID)
  WHERE HD_TICKET.IS_PARENT != 0
  GROUP BY HD_TICKET.ID)
FILTER ON (FILTER.ID = HD_TICKET.ID)
WHERE FILTER.CSTATE not rlike 'opened|stalled'
AND HD_STATUS.STATE not rlike 'closed'

_______________________________

*Update Query*

UPDATE HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT DISTINCT HD_STATUS.NAME AS FNAME, HD_STATUS.ID AS HD_STATUS_ID,
  HD_TICKET.HD_QUEUE_ID AS FQUEUE, CHILD.CNAME, CHILD.CID, CHILD.CQUEUE
  FROM HD_STATUS
  JOIN HD_TICKET ON (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
  JOIN
   (SELECT DISTINCT HD_STATUS.NAME AS CNAME, HD_STATUS.ID AS CID,
    HD_TICKET.HD_QUEUE_ID AS CQUEUE, HD_TICKET.PARENT_ID
    FROM HD_TICKET
    JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    WHERE HD_TICKET.PARENT_ID != 0)
  CHILD ON (CHILD.CNAME = HD_STATUS.NAME)
  WHERE HD_TICKET.IS_PARENT != 0)
FILTER ON (FILTER.FQUEUE = HD_TICKET.HD_QUEUE_ID)
SET HD_TICKET.HD_STATUS_ID = FILTER.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
Answered 11/01/2012 by: jverbosk
Red Belt

  • In further testing, I have determined that this ticket rule needs to be created in the queue where the parent tickets are located. It still works across queues in regards to querying the child tickets for status and then applying the relative closed STATUS.ID to the parent ticket when all child tickets are closed.

    Also, this runs on (parent) ticket save or when run manually. I played with getting this to work (1) on a schedule and (2) on ticket save when the child tickets were saved (in a closed state), with this ticket rule setup in the child tickets' queue (Queue 1 in my setup), but the parent ticket (in Queue 2) did not get updated.

    I'll explore why this behavior occurs in the blog article I'll start shortly to explain this ticket rule.

    John
  • Blog is up, for anyone who'd like to understand how this works and how to tweak it:

    http://www.itninja.com/blog/view/k1000-custom-ticket-rules-closing-parent-tickets-automatically-when-all-child-tickets-are-closed

    John
Please log in to comment

Answers

1

I am looking at number 1 as well. I have some administrative processes assigned to multiple users; when they close their respective child tickets I would like the parent ticket to close. 

Answered 10/23/2012 by: JordanSPE
White Belt

  • I'm working on a similar issue myself. From what I can glean you need to create a custom rule on the child tickets, that when they close they email the parent ticket that they are finished. When the parent ticket gets the designated comment inserted from the email a ticket rule on the parent ticket would then close the parent.
Please log in to comment
1

I have a number of parent-child ticket rules "update" rules documented in my primary Service Desk queue, maybe something here (combined with the other posts) will help connect the dots:

http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

http://www.itninja.com/question/ticket-rule-to-synchronize-status-of-referrers-tickets-with-current-ticket

http://www.itninja.com/blog/view/k1000-custom-ticket-rules-using-an-inline-view-to-query-update-tables-in-one-pass

The last two links are for something similar to your request (assigning status of a ticket to referring tickets) - although they don't use the parent/child columns, the rule(s) could be tweaked to use the parent/child ticket rule setup documented in the Service Desk config to accomplish what you want.

If nothing else, have a go at it and post what you end up with.

John

Answered 10/26/2012 by: jverbosk
Red Belt

Please log in to comment
1

OK, I saw no response to this so I thought I'd help out a bit more.  ^_^

See if this takes care of number 1 for all of you (written based on what I referenced earlier).  I didn't test the update query, but it *should* work based on the variations of the select query that led to it.  Assuming it works - if you want an explanation, let me know and I'll write it up as a blog.

The select query shoud return all parent tickets when all of the corresponding child tickets status is set to "closed".  Any parent tickets with child tickets in other states will not be returned.  You could set this up to run on ticket save, but be aware that if all child tickets are not closed, then the parent ticket will not be closed.  Therefore, you may want to run just the select query (emailing the results to an email address, in the MySQL Query Browser, or as a report) to verify the select query is working as expected, then run it with the update query (manually) to take care of any tickets that need updated (i.e. cleanup), prior to setting it up to run on ticket save.

I should also note that this ticket rule should be setup in your first queue, so that it will work across *all* queues.  If you want to restrict the queues it runs against, just extend the WHERE statement in the select query.

Hope that helps!

John

_______________________________________

*SELECT QUERY*
SELECT HD_TICKET.ID, S.STATE, FILTER.CSTATE
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN (SELECT HD_TICKET.ID, GROUP_CONCAT(CHILD.STATE) AS CSTATE
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN (SELECT HD_TICKET.ID, S.STATE, HD_TICKET.PARENT_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
WHERE HD_TICKET.PARENT_ID != 0) CHILD ON (CHILD.PARENT_ID = HD_TICKET.ID)
WHERE HD_TICKET.IS_PARENT != 0
GROUP BY HD_TICKET.ID) FILTER ON (FILTER.ID = HD_TICKET.ID)
WHERE FILTER.CSTATE not rlike 'opened|stalled'

*UPDATE QUERY*
UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN (SELECT HD_TICKET.ID, HD_TICKET.HD_STATUS_ID, HD_TICKET.PARENT_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
WHERE HD_TICKET.PARENT_ID != 0) CHILD on (CHILD.PARENT_ID = HD_TICKET.ID)
SET HD_TICKET.HD_STATUS_ID = CHILD.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)

_______________________________________

Here's a couple of versions of the select query with some more details, in case you want to follow my suggestion and run initially to verify what is being returned.

This version will return *all* parent and child tickets (just for verification):

SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.PARENT_ID,
S.NAME AS STATUS_NAME, S.STATE AS STATUS_STATE,
HD_TICKET.HD_STATUS_ID, CHILD.ID AS CHILD_ID,
CHILD.TITLE AS CHILD_TITLE, CHILD.NAME AS CHILD_STATUS_NAME,
CHILD.STATE AS CHILD_STATUS_STATE
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  S.STATE, HD_TICKET.HD_STATUS_ID, HD_TICKET.PARENT_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  WHERE HD_TICKET.PARENT_ID != 0)
CHILD on (CHILD.PARENT_ID = HD_TICKET.ID)
WHERE HD_TICKET.IS_PARENT != 0

_______________________________________

This one will *only* return parent/child tickets when the child tickets are closed (what the actual working select query above does and what the update query will receive):

SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.PARENT_ID,
S.NAME AS STATUS_NAME, S.STATE AS STATUS_STATE,
HD_TICKET.HD_STATUS_ID, CHILD.ID AS CHILD_ID,
CHILD.TITLE AS CHILD_TITLE, CHILD.NAME AS CHILD_STATUS_NAME,
CHILD.STATE AS CHILD_STATUS_STATE
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  S.STATE, HD_TICKET.HD_STATUS_ID, HD_TICKET.PARENT_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  WHERE HD_TICKET.PARENT_ID != 0
  AND S.STATE not rlike 'opened|stalled')
CHILD on (CHILD.PARENT_ID = HD_TICKET.ID)
WHERE HD_TICKET.IS_PARENT != 0

Answered 10/28/2012 by: jverbosk
Red Belt

  • Note - I tested the update query and while it works fine when the parent and child tickets are in the same queue, it does not work across queues (due to the STATUS.ID column having different values for different queues). I'm looking into this now and will update when I've figured out a solution.

    John
Please log in to comment
1

For point 2, I believe there's a queue setting available that will automatically close all child tickets when the parent ticket is closed.  Personally, I would look into using this to address this specific situation as a ticket rule would either need to be tweaked each time to target specific ticket numbers, or would need to target specific statuses that the tickets have initially, which would have to differ from what they have once they are "in production" and this could be potentially messy if other tickets with the same status are unintentionally updated (i.e. closed).  Just closing the parent ticket manually and having the child tickets close automatically would seem to be the safest (and least time-consuming) approach that I can think of at the moment, but if you can provide further criteria that can be targeted, I'll see if there might be other options.

John

Answered 10/28/2012 by: jverbosk
Red Belt

  • Hey jverbosk,

    Thank you very much, I will go ahead and test this. I'll get back to you with an update.

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