K1000 Custom Ticket Rules - Closing Parent Tickets Automatically When All Child Tickets Are Closed
______________________________________________________________________________

A request came up recently to develop a way to (automatically) close parent tickets when all child tickets have been closed.  Although there is a Service Desk queue option to automatically close child tickets when a parent ticket is closed, there is nothing built-in which addresses the reverse scenario.  Although my approach leaves a couple things to be desired (mainly, the ability to run this ticket rule on a schedule or have the parent ticket closed when the last child ticket is closed), it does work and is honestly the closest solution I've seen.  After reading through this, if anyone has any suggestions or modifications to accomplish this, I'd definitely be interested!

Anyways, in this article, I'll explain how to develop a query strategy, how to construct "interim" queries and (finally) how to put everything together in a custom ticket rule.
________________________

Original question:
http://www.itninja.com/question/parent-and-child-ticket-custom-rule
________________________

For a full analysis and explanation of inline views and other advanced MySQL techniques:

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables
http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables
________________________

For more basic SQL query construction tips and MySQL Query Browser setup:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
______________________________________________________________________________

1) Developing a plan

The first part of constructing complex queries is to determine exactly what conditions need to be met and how to address each of these with joins to the appropriate tables, filters to remove undesired results and subqueries to pull data that is not possible in the main query.  As such, here are the pre-requisites.
________________________

In the Select query:

- find all child tickets
- find all parent tickets
- list parent tickets with corresponding child tickets
- set up the parent/child query for filtering

A note on parent/child ticket relationships - although there are columns in the HD_TICKET table to define parent tickets (HD_TICKET.PARENT_ID & HD_TICKET.IS_PARENT), there is nothing similarly explicit to define child tickets.  Therefore, we'll need to come up with a strategy to work around this limitation.
________________________

In the Update query:

- find child ticket statuses
- relate child ticket statuses to parent ticket statuses
________________________

A note on HD_TICKET.HD_STATUS_ID - this (and the corresponding HD_STATUS.ID) is the value that defines all other STATUS values for a ticket.  Since we want to change specific tickets, we are limited to updating a value in the HD_TICKET table, as updating values in the HD_STATUS table would apply to *all* tickets.  And this value changes for different queues.  

Using my Service Desk queues for an example - the child tickets in queue 1 have an HD_STATUS.ID of 2 that corresponds to an HD_STATUS.NAME value of Closed, but the parent tickets in queue 2 have an HD_STATUS of 12 that corresponds to the same HD_STATUS.NAME value of Closed.  So that's definitely something to take into consideration, ideally without hard-coding values into the query.
______________________________________________________________________________

2) Interim queries for the SELECT query

Interim queries are the "working parts" that allow the main queries to do what we need.  I prefer to use inline views (uncorrelated subqueries) as these are pretty much guaranteed to be executed in order of depth (i.e. I can control the query execution path a bit more) and because I can run them on their own to verify what will be passed to the upper queries.
________________________

Finding all child tickets

The following query will find all child tickets, courtesy of the WHERE HD_TICKET.PARENT_ID != 0 statement.  It effectively says "show me all of the tickets that have a parent ticket" - i.e. child tickets.  Take note of the HD_TICKET.HD_STATUS_ID value - again, this is how the ticket's status name and status state are applied (via the HD_STATUS reference table).  Also be aware that I truncated the HD_TICKET.TITLE results below so they would all fit here - the actual *long* values are not identical.

*Query*
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_STATUS.NAME,
HD_STATUS.STATE, HD_TICKET.HD_STATUS_ID,
HD_TICKET.HD_QUEUE_ID, 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

*Example Output*
ID     TITLE      NAME    STATE   HD_STATUS_ID  HD_QUEUE_ID  PARENT_ID
24426  New User*  Closed  closed  2             1            24425
24427  New User*  Closed  closed  2             1            24425
24428  New User*  Closed  closed  2             1            24425
24433  New User*  Closed  closed  2             1            24432
24434  New User*  Closed  closed  2             1            24432
24435  New User*  Closed  closed  2             1            24432
24453  New User*  Open    opened  1             1            24452
24454  New User*  Closed  closed  2             1            24452
24455  New User*  Closed  closed  2             1            24452
etc...
________________________

Finding all parent tickets

This is basically the same as the last query, but uses WHERE HD_TICKET.IS_PARENT != 0 to return all of the parent tickets.  Note that HD_TICKET.PARENT_ID has been left out since none of these tickets have parents (in my system).  Take particular notes of the HD_STATUS_ID values (and how they differ from the child tickets due to the HD_QUEUE_ID values being different) and the HD_TICKET.ID values (and how they correspond to the HD_TICKET.PARENT_ID values in the earlier query results).

*Query*
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_STATUS.NAME,
HD_STATUS.STATE, HD_TICKET.HD_STATUS_ID,
HD_TICKET.HD_QUEUE_ID
FROM HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE HD_TICKET.IS_PARENT != 0

*Example Output*
ID     TITLE      NAME      STATE   HD_STATUS_ID  HD_QUEUE_ID
24425  New User*  Closed    closed  12            2
24432  New User*  Approved  opened  20            2
24452  New User*  Approved  opened  20            2
etc...
________________________

Listing parent tickets with corresponding child tickets

This query combines the last two queries (using the first as an inline view - CHILD) and is truly an "interim" query, as this (in its current state) was more for confirmation/feasibility purposes than pure usage in the main query.  However, it was an essential step in developing the later queries as it confirmed that pulling this data together was possible.  Although it necessitated scrolling through results, I still included the HD_TICKET.TITLE so that the child/parent relationships are much easier to follow for human eyes (as the parent ticket's title is listed for each child ticket).

The most important thing to consider when constructing inline views is how they will JOIN to the main query, and in this case there was a nice match between the CHILD view's HD_TICKET.PARENT_ID value and the (PARENT) outer query's HD_TICKET.ID value - which should make sense as the previous queries' output illustrated.

*Query*
SELECT HD_TICKET.ID AS PID, HD_TICKET.TITLE AS PTITLE,
HD_STATUS.NAME AS PSNAME, HD_STATUS.STATE AS PSSTATE,
HD_TICKET.HD_STATUS_ID AS PSID, HD_TICKET.HD_QUEUE_ID AS PQUEUE,
CHILD.ID AS CID, CHILD.PARENT_ID AS CPID, CHILD.TITLE AS CTITLE,
CHILD.NAME AS CSNAME, CHILD.STATE AS CSSTATE,
CHILD.HD_STATUS_ID AS CSID, CHILD.HD_QUEUE_ID AS CQUEUE
FROM HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_STATUS.NAME,
  HD_STATUS.STATE, HD_TICKET.HD_STATUS_ID,
  HD_TICKET.HD_QUEUE_ID, 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

*Example Output*
- I'm putting this in two lines divided by parent (P) & child (C), since it's pretty long.

PID    PTITLE     PSNAME    PSSTATE  PSID  PQUEUE  (next section)
24425  New User*  Closed    closed   12    2
24425  New User*  Closed    closed   12    2
24425  New User*  Closed    closed   12    2
24432  New User*  Approved  opened   20    2
24432  New User*  Approved  opened   20    2
24432  New User*  Approved  opened   20    2
24452  New User*  Approved  opened   20    2
24452  New User*  Approved  opened   20    2
24452  New User*  Approved  opened   20    2
etc...

CID    CPID   CTITLE     CSNAME  CSSTATE  CSID   CQUEUE
24426  24425  New User*  Closed  closed   2      1
24427  24425  New User*  Closed  closed   2      1
24428  24425  New User*  Closed  closed   2      1
24433  24432  New User*  Closed  closed   2      1
24434  24432  New User*  Closed  closed   2      1
24435  24432  New User*  Closed  closed   2      1
24453  24452  New User*  Open    opened   1      1
24454  24452  New User*  Closed  closed   2      1
24455  24452  New User*  Closed  closed   2      1
etc...
________________________

Setting up the parent/child query for filtering

Since the main goal of the ticket rule's Select query is to only return parent tickets when all child tickets have been closed, we need a method to filter out any that don't match this criteria.  I did this by adding two functions - GROUP_CONCAT (to concatenate the child tickets' HD_STATUS.STATE values into a single column) and GROUP BY (so results are "broken up" - otherwise there's only a single row of output).  I've left all extraneous columns out at this point, since the previous query confirmed things are working as required.

*Query*
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

*Example Output*
ID     CSTATE
24425  closed,closed,closed
24432  closed,closed,closed
24452  opened,closed,closed
etc...
______________________________________________________________________________

3) Constructing the ticket rule's SELECT query

At this point, we have everything necessary to construct the Select query below - related parent/child tickets and a method to filter.  So now it's just a matter of filtering on the results a bit.

The previous interim query was added as an inline view (FILTER) and we can at this point filter using a standard REGEXP (rlike) statement - WHERE FILTER.CSTATE not rlike 'opened|stalled' (i.e. only return parent tickets if the child tickets are *all* closed).  Using the previous query's output as an example, the parent ticket with an ID of 24452 would not be returned by this query, since one of its child ticket's states is "opened".

In case you wondered why I chose to GROUP_CONCAT on HD_STATUS.STATE, it's because this is the only column that remains consistent throughout queues and customizations - for example, in my IT queue I have several status names with a state of opened, but only one for closed.

Going one step further, I added AND HD_STATUS.STATE not rlike 'closed' to filter out any parent tickets that were already closed - no sense including them in the results since the ultimate goal is to close the tickets returned by this query.

*Final Select Query*
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'

*Example Output*
ID     NAME      STATE   CSTATE
24432  Approved  opened  closed,closed,closed
etc...
______________________________________________________________________________

4) Interim queries for the UPDATE query

OK, we have the results from the Select query, now it's just a matter of setting the parent's HD_TICKET.HD_STATUS_ID value to match the child's HD_TICKET.HD_STATUS_ID value and... oh wait, they're different in different queues (and *yes* I found this out the hard way during testing when I was getting ready to write this article...).  So now, not only do we need to update the Select'd parent tickets, we also need to figure out a way to relate the child tickets' queue's HD_TICKET.HD_STATUS_ID value to the parent tickets' queue's HD_TICKET.HD_STATUS_ID value.  Well then - on with the interim queries!
________________________

Finding child ticket statuses

Same idea as the very first query, but this time we are primarily interested in the status of the child tickets and not the tickets themselves.  As the JOINs will get a little... interesting... I'm using DISTINCT to keep results to a minimum.  I'm also including HD_TICKET.PARENT_ID just to illustrate a bit - note that there's only one listing for each child ticket "group", except for 24452 which has two different values for HD_STATUS.NAME.

*Select Query*
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

*Example Output*
NAME    ID  HD_QUEUE_ID  PARENT_ID
Closed  2   1            24425
Closed  2   1            24432
Open    1   1            24452
Closed  2   1            24452
etc...
________________________

Relating child ticket statuses to parent ticket statuses

This is the "interesting" part I was referring to earlier.  This is basically the same as what I did with the child ticket query to the parent query join for the ticket rule's SELECT query, but here I'm focusing only on the statuses for the queues that have parent and child tickets.  As you can see in the first line of results, there is the relationship I needed - the HD_TICKET.HD_STATUS_ID value (12) for the parent tickets' queue (2) corresponds directly to the child tickets' ID value (2) and queue (1).  The second line is an undesirable result, mainly due to doing such a non-specific join on values that don't correspond directly - but this will be filtered out in the ticket rule's UPDATE query since the parent tickets being returned by the SELECT query will only be in queue 2.  It would be possible to filter further by adding AND HD_TICKET.HD_QUEUE_ID != 1 to the end, so it's something to keep in mind if you need to tweak this further for your own environment should you be dealing with multiple queues with parent tickets.  Note the addition of another DISTINCT in the upper query and away we go...

*Select Query*
SELECT DISTINCT HD_STATUS.NAME AS PNAME, HD_STATUS.ID AS PID,
HD_TICKET.HD_QUEUE_ID AS PQUEUE, 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

*Example Output*
PNAME   PID  PQUEUE  CNAME   CID  CQUEUE
Closed  12   2       Closed  2    1
Closed   2   1       Closed  2    1
______________________________________________________________________________

5) Constructing the ticket rule's UPDATE query

Everything is now in place for the UPDATE query.  One thing to keep in mind is that the K1000 requires certain table and column names to be default (for example HD_TICKET and HD_STATUS_ID), so that's why you'll see me break my standard (preferred) routine of aliasing tables and column names more.  I also left them alone as much as possible for clarity, since there's already enough going on with the inline views, but you can pretty much alias everything except those two.  

As I noted in my last ticket rule blog about table aliases introducing problems in ticket rules, I found the same to be true this time around when trying to update the parent ticket's HD_STATUS.ID value - which is why you see HD_STATUS.ID being aliased as HD_STATUS_ID instead of PID like the rest.  Fortunately, HD_STATUS.ID directly corresponds to HD_TICKET.HD_STATUS_ID, so this worked out nicely (almost as if I'd intended it...).

*Update Query*
UPDATE HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT DISTINCT HD_STATUS.NAME AS PNAME, HD_STATUS.ID AS HD_STATUS_ID,
  HD_TICKET.HD_QUEUE_ID AS PQUEUE, 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.PQUEUE = HD_TICKET.HD_QUEUE_ID)
SET HD_TICKET.HD_STATUS_ID = FILTER.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)

*Example Run Log Output*
47:31> Starting: Fri, 02 Nov 2012 15:47:31 -0400
47:31> Executing Select Query...
47:31>   selected 1 rows
47:31> Executing Update Query...
47:32>   updated 1 rows
47:32> Ending: Fri, 02 Nov 2012 15:47:32 -0400

Yes, that's parent ticket (ID 24432) that was open earlier.  Re-running the "find all parent tickets" query from the second section now shows it has been closed and that it has the correct HD_TICKET.HD_STATUS_ID value of 12 (and not the child ticket's HD_TICKET.HD_STATUS_ID value of 2).

*Example Output*
ID     TITLE      NAME      STATE   HD_STATUS_ID  HD_QUEUE_ID
24425  New User*  Closed    closed  12            2
24432  New User*  Closed    closed  20            2
24452  New User*  Approved  opened  20            2
etc...
______________________________________________________________________________

6) Setting up the ticket rule

There are a couple of things to be aware of when setting up this ticket rule.  First, I determined that it needs to be created in the queue where the parent tickets are located - so if you have multiple queues with parent tickets, it will need added to each.  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), but since it is only updating the parent tickets it needs to be in their queue(s).

Also, this runs on (parent) ticket save or when run manually via the ticket rule's screen (the Run Now button).  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), as well as 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 (actually, nothing got selected, since it couldn't access the tickets in queue 2).

Now that I have this written up, I'll bounce this off of a couple of KACE gurus and see if there might be some change that would make my preferred solution possible (i.e. setup the ticket rule once in the first queue and have it run either on a schedule or when the last child ticket is closed).  As it stands, I'm not sure how to do it since a ticket rule appears to only be able to update a ticket in its own queue.
______________________________________________________________________________

7) Custom Ticket Rule Construction

*Title*
Parent Ticket Queue - Close Parent Ticket When All Child Tickets Closed

*Order*
100

*Notes*
Select query selects all parent tickets when all child tickets have been closed.  Update query pulls relative status ID from child tickets and applies to parent tickets (i.e. closed status ID in queue 1 = 2, but in queue 2 = 12).  Only runs when parent ticket saved or when ticket rule run manually.

*Frequency*
on Ticket Save (and manually via Run Now)

*Enabled*
<checked>

*Select Query*
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'

X Run an update query, using the results from the one above

*Update Query*
UPDATE HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN
 (SELECT DISTINCT HD_STATUS.NAME AS PNAME, HD_STATUS.ID AS HD_STATUS_ID,
  HD_TICKET.HD_QUEUE_ID AS PQUEUE, 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.PQUEUE = HD_TICKET.HD_QUEUE_ID)
SET HD_TICKET.HD_STATUS_ID = FILTER.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
______________________________________________________________________________

Hope that helps!

John