KACE Product Support

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>)

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. 

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

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

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