/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Blog Posts tagged with K1000 Ticket Rules

Ask a question

KACE How To Video Series: Dell KACE and Bomgar Integration Video

Summary:  A video from Bomgar explaining the functionality of Bomagar with the KACE K1000 appliance.  Video is 16:59 minutes.

View comments (2)

K1000 Custom Ticket Rules - Using an Inline View to Query & Update Tables in One Pass

K1000 Custom Ticket Rules - Using an Inline View to Query & Update Tables in One Pass
______________________________________________________________________________

A question recently came up regarding synchronizing a field between tickets that reference other (earlier) tickets, and one of my fellow ninjas advised that this would require reading a value from the first ticket and then applying that value to the referring ticket(s), something that isn't possible with MySQL.  While this is definitely true for a "simple" query, inline views (uncorrelated subqueries) actually make this possible through the use of a temporary table that the main ("simple") query can then reference.

In this article, I'll explain how to determine which tables to use, how to construct "interim" queries and (finally) how to put everything together in a custom ticket rule.
________________________

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

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) Determining which tables to use

Since the original question dealt with Service Desk tickets and their statuses, the first two tables we'll need to consider are HD_TICKET (where the ticket ID, title, ticket statuses and other info are kept) and HD_STATUS (where the ticket statuses are actually defined).  The other important point concerned referencing tickets (the See Also: field in the GUI), and this information is kept in the HD_TICKET_RELATED table (which lists all tickets having something listed or being referenced in the See Also: field).

Getting these all together in a single query requires the use of JOIN statements on common data.  Since we'll be querying and updating the tickets themselves, it makes sense to use HD_TICKET as the main table in the query (i.e. FROM HD_TICKET).  This leaves us with joining the other tables.  HD_STATUS is pretty straightforward, as HD_STATUS.ID directly corresponds to HD_TICKET.HD_STATUS_ID, so we can use JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID) to enable pulling data from this table.
________________________

However, with HD_TICKET_RELATED, we have two possible columns to join on - FROM_ID and TO_ID.  So, which one should be used?  A simple query and review of the tickets themselves will help us determine this:

*Example Query*
SELECT * FROM HD_TICKET_RELATED

*Example Output*
FROM_ID   TO_ID
21903     19968
etc...
________________________

It would make sense that the higher order ticket ID (21903) would be the referencing ticket, and reviewing the ticket in the Service Desk confirms this - the See Also: field has the 19968 ticket listed.  Therefore, since we want to update the referencing ticket, we'll note this as such:

R.FROM_ID = "referring ticket" has the See Also ticket listed
R.TO_ID = "referenced ticket" the See Also ticket that is referenced

For example:

FROM (Referring)           > 21903
TO (See Also - Referenced) > 19968
______________________________________________________________________________

2) Constructing interim queries

First, we'll build our main query using the FROM_ID column for the join, so that we pull (SELECT) all of the referring tickets.  I've also included the ticket ID, ticket title (for easy reference), status name and status ID for clarity:

SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
ORDER BY HD_TICKET.ID
________________________

The next step will be to pull the same data from the referenced ticket - in this case we're mainly concerned with HD_STATUS_ID, although it could be any column.  This can be done with the following query:

SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID)
ORDER BY HD_TICKET.ID
________________________

Note that this is *exactly* the same as the previous query, the only difference being that HD_TICKET_RELATED has been joined using the TO_ID column to pull (SELECT) all of the referenced tickets.
______________________________________________________________________________

3) Constructing the ticket rule's SELECT query

OK, so now we have two queries - one for the referring tickets and the next for referenced tickets.  Now what?  Well, remember that the goal is to pull the status from the referenced tickets and then apply it to the referring tickets.  So we'll put the referenced tickets in an inline view (SOURCE) so that the main (referring) query can access this data (and later apply it using an update query).  The original SELECT query is as follows, but as you'll see there's actually a better way to construct it:

SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

This results in the same fields being listed for both the referenced tickets (SOURCE) and the referring table, in a nice arrangement that allows for quick comparison of the ID, title and status columns.  Note that it is ultimately the HD_TICKET.HD_STATUS_ID column that we want to change (UPDATE) in the referring tickets, as this is where a ticket's status is kept - all further details (such as the actual status name) are referenced in the STATUS table.  The WHERE statement simply says "ignore tickets with matching status IDs", which will focus the UPDATE query to only run against tickets that need to be updated (i.e. those whose status IDs don't match).
________________________

One of the main considerations with inline views is determining which columns to use for the JOIN to the main query.  As with any JOIN, we want to use columns that share common elements without being too generic.  Using the above query as an example, STATUS.NAME meets the first criteria (same data in the main query and inline view), but fails on the second (not granular/specific enough).  HD_TICKET.TITLE meets the second criteria (very specific), but typically will fail on the first (unless there is a guarantee that the titles of referencing tickets are *exactly* the same as the referenced tickets - personally, I wouldn't bet on this as it's an open text field).

The only columns that actually meet both criteria are HD_TICKET.ID, HD_TICKET_RELATED.FROM_ID and HD_TICKET_RELATED.TO_ID - which makes sense as these are the primary keys for their respective tables.  So the question now is - which to use?  Trial and error will get the job done (as I did with the original query above!), however it's always good to step back and review things again later to make sure the construction makes sense (note to self... ^_^).  Keep in mind that we want to target the referring tickets, so joining to the main query's HD_TICKET.ID column seems natural enough.  

From there, it's basically process of elimination.  We know that the referenced tickets have different IDs than the referring tickets, so we can't use SOURCE.ID (the inline view's HD_TICKET.ID) for the join.  The same reasoning applies to using SOURCE.TO_ID, the inline view's HD_TICKET_RELATED.TO_ID column (not there, but we *could* add it).  Recall that TO_ID specifies the referenced tickets, while the main query's HD_TICKET.ID column specifies the referring tickets, and you'll see that it's no better than trying to use SOURCE.ID.  So that leaves us with SOURCE.FROM_ID, which specifies the referring tickets and lines up exactly with the results of HD_TICKET.ID in the main query (meeting both criteria, as discussed a moment ago).  

Now the issue is - we don't have FROM_ID in the inline view.  Not a problem, just add it in - and keep in mind that columns listed in the inline view do not need to be called in the main query (sometimes they are just "utility" columns for JOINs, etc).  This results in a more efficient query than the one above, as the JOIN between the main query and the inline view are now a 1-to-1 match and no DISTINCT cleanup is required:

SELECT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

The "problem" with the original query (and often the need for DISTINCT will highlight this fact) is that the JOIN on the main query and inline view ended up with some extra results whenever there were multiple referring tickets for one referenced ticket.  I wasn't initially aware of this circumstance and (trying to get it working quickly) "fixed" it with a DISTINCT statement, but the more efficient/elegant solution is to prevent the extraneous results in the first place (as is done in the second SELECT query).  This is where constructing queries becomes more of an art than a science, as both return the same results.  But adding extra "informational" columns (such as TITLE) can be a great help in making sense of what the query returns.
________________________

I should note that the comment was made in the question that this was a complicated SELECT query, and while it is true that it is more complex than absolutely necessary for the SELECT query, building it out like this was in fact necessary to derive the UPDATE query.  For anyone interested, the most basic functional SELECT query would only consist of the HD_TICKET table and the JOIN to the HD_TICKET_RELATED table on FROM_ID, so that the referring tickets will be listed:

SELECT HD_TICKET.ID
FROM HD_TICKET
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)

That being said, if there are a number of referring tickets, I would still use the "complicated" query so that only tickets without matching HD_STATUS_ID values will be passed to the UPDATE query.  The "complicated" query is also perfect for running as a SQL report, should the need arise.
______________________________________________________________________________

4) Constructing the ticket rule's UPDATE query

With the SELECT query constructed, it is just a matter of changing it slightly to create the UPDATE query.  In this case, specify the HD_TICKET table for the UPDATE statement, specify the HD_STATUS_ID columns in the SET statement and (finally) include the WHERE HD_TICKET.ID in (<TICKET_IDS>) statement so it will only apply to the ticket that is being saved (as the rule is set to run on ticket save).  Aside from HD_TICKET.ID and HD_TICKET.HD_STATUS_ID, the other columns in the inline view are most likely unnecessary (for what it's worth), so feel free to experiment and whittle it down further as you see fit.

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE,
  HD_TICKET.HD_STATUS_ID, S.NAME
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
________________________

Another version of the UPDATE query that should work (based on the revised SELECT query above):

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
________________________

As the original author of the question found, updating (synchronizing) additional fields just requires adding the column(s) to the inline view and to the SET statement.  Here is a variation that also updates the ticket's owner (HD_TICKET.OWNER_ID):

UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE,
  HD_TICKET.HD_STATUS_ID, S.NAME, HD_TICKET.OWNER_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID,
HD_TICKET.OWNER_ID = SOURCE.OWNER_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
______________________________________________________________________________

5) Beware of aliasing the updated table in ticket rules

One "gotcha" I ran across (my "Arg..." comment in the original question) was related to the SELECT query not working properly on ticket save with the HD_TICKET table aliased.  My original construction was like this:

SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
FROM HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
WHERE T.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY T.ID

UPDATE HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE,
  T.HD_STATUS_ID, S.NAME
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET T.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE T.ID in (<TICKET_IDS>)
________________________

It worked fine in the MySQL Query Browser *and* when I ran the ticket rule manually.  However... when the ticket was saved (and the ticket rule ran on ticket save), the following error popped up in the ticket rule's Run Log field:

08:24> Starting: Fri, 05 Oct 2012 15:08:24 -0400
08:24> Executing Select Query...
08:24> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'on clause'] in EXECUTE("SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME,SOURCE.HD_STATUS_ID,
T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
FROM HD_TICKET T
JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID)
JOIN
 (SELECT T.ID, T.TITLE, S.NAME, T.HD_STATUS_ID
  FROM HD_TICKET T
  JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID))
SOURCE on (SOURCE.ID = R.TO_ID) and (HD_TICKET.ID = 21903) ")

Fortunately, I recalled running into this before (many moons ago...) and removing the aliases from the SELECT query took care of things.  I did the same for the UPDATE query for consistency, although this apparently worked with the aliases in place (as was noted prior to this change, and as I've seen done in other UPDATE queries).
______________________________________________________________________________

6) Custom Ticket Rule Construction

*Title*
Synch Ticket Status With Reference Ticket Status

*Order*
50

*Frequency*
on Ticket Save

*Enabled*
<checked>

*Select Query*
SELECT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

*Update Query*
UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.HD_STATUS_ID, R.FROM_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.FROM_ID = HD_TICKET.ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)
______________________________________________________________________________

Hope that helps!

John

View comments (2)

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

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

View comments (10)

Creating True SLA Timers in the KBOX Helpdesk - Part 1: What you really want from your data

When running a helpdesk you often live and die by certain metrics.  This is true if your customers are internal or external.  Arguably the most important metrics relate to the amount of time that a ticket spends in a particular state. This is used to track internal / external Service Level Agreements (SLAs) and a large part of employee performance evaluations.

The K1000 Management Appliance's Service Desk does have some fields to monitor times. For example, it has a field in the HD_TICKET table called TIME_OPENED. Some see the fields in the database and expect them to keep track of the total time that a ticket was opened (etc) or when it was first opened (etc). However, this is not the case. Specifically, the TIME_OPENED field tracks the most recent timestamp that the ticket was moved into an opened state from an non-opened state. When a ticket changes states the relevant column automatically gets updated. The exception is if the change in status happens in ticket rules -- then the rule is responsible for keeping track. A properly written rule would do this by updating TIME_OPENED (and other fields) appropriately.  If a ticket moves out of a given state then the value stored for the previous state will remain. However, if a ticket changes back to a state it has occupied before then that value will be updated. Therefore, you can never know for sure with a query how long a ticket has been in a given state.

A ticket will always have at least one state – equivalent to the state of the default Status but is not guaranteed to have all three states (or even two of them). For example, a ticket that is opened in a closed state and never changes will never get a non-zero value for TIME_OPENED nor TIME_STALLED.

If you want to keep track of that then you would need to do that yourself with ticket rules.

What I am going to provide in this series of posts are rules that most of you will be able to use verbatim. These rules will help you to create a SLA foundation for reports and other rules.

Summary of Data You Might want to Track and Report on

Statistic

Method of Discovery

Time a ticket was created

HD_TICKET.CREATED

Time a ticket was last modified at the database level***

HD_TICKET.MODIFIED

Time a ticket was last moved into an opened state from a non-opened state*

HD_TICKET.TIME_OPENED

Time a ticket was last moved into an stalled state from a non-stalled state*

HD_TICKET.TIME_STALLED

Time a ticket was last moved into an closed state from a non-closed state*

HD_TICKET.TIME_CLOSED

Time a ticket was last moved into an opened state

not available in the product

Time a ticket was last moved into an stalled state

not available in the product

Time a ticket was last moved into an closed state

not available in the product

Total seconds since a ticket was created

UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED)

Total seconds since a ticket was modified***

UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.MODIFIED)

Total seconds a ticket has been in an opened state**

this series will show you how to do this

Total seconds a ticket has been in an stalled state**

this series will show you how to do this

Total seconds a ticket has been in an closed state**

this series will show you how to do this

*Note: If status is modified in rules then you will have to track this in your rules 

**Note: that data is only reliable for tickets created after the rules are in place

*** Note: the modified time may be updated even when you do not see an update in the ticket. We will create an alternative for the MODIFIED timestamp

Coming Soon: Part II - Considerations for the Rules we will create


disclaimer: all of my posts are my own personal opinion (and sometimes even insight).  I give this information to the community to support. Even though I work for Dell Kace there is no implied contract between Dell Kace and you over the support of this information.  I will often refer to core features of the product that are supported by engineering and technical support but I will also often refer to information that pushes the product beyond its specification. Any technical documents that I write about supported information I will continue to do so via our infrastructure for officially supported material (e.g. knowledge base articles) which is at http://www.kace.com/support.  You are welcome to contact me directly about this information but you will have the most success by responding with comments directly on my posts where I or the community can help.

View comments (2)

Creating True SLA Timers in the KBOX Helpdesk -Part II - Considerations for the Rules we will create

Let's discuss what rules and fields we will need to create.  In part IV  we will discuss an adjustments we might need to make to existing rules we already have.

1. Create Custom Field to Hold Timer Data

The following data needs to be tracked:

  • timestamp the ticket was last modified by any ticket change -- this will replace the meaning of our traditional "modified" field. This is necessary because the rules will update the ticket counter periodically which will update the modified time. This means the modified time will now reflect either the last time a rule modified the ticket or the last time a user modified it, whichever is greater.
  • The counter(s)* itself. This will track the time the ticket has been in an opened state. If you want to track the total time a ticket has been stalled then you could have a separate counter. And then there are many customers who are interested in the combination of the two which could be obtained by summing them OR modifying the rules to track the combination in one counter. In our example we are going to have one timer to track "total hours opened" and another for "total hours stalled".
  • timestamp of when the counter was last updated. This will allow us to keep the timer running efficiently without doing too many calculations through complex queries.

* there are some caveats we'll talk about later.  We are creating 4 custom fields but 5.4 can handle many custom fields so I recommend 5.4

Here is an example of setting up the fields at Service Desk->Configuration->Queues->(pick a queue)->Custom Fields and Layout->Ticket Layout section. 

Note that in the database field CUSTOM_15 is stored as HD_TICKET.CUSTOM_FIELD_VALUE14 because one system starts counting at 1 and the other at 0. You will see this throughout the SQL of the rules.  If you are using different custom fields then you will have to modify the queries in the rules

2. Decide How to Deal with existing Tickets in your helpdesk

If you do not have any tickets in helpdesk queues you want to use this for then this is irrelevant. If you do have helpdesk tickets in this queue then here are your choices:

  • Delete all tickets in this queue
  • Modify the rules (in SQL) to only act upon tickets above a certain number
  • Populate all the existing tickets with baseline data. Running the rule called "Populate Existing Tickets with Baseline Data" will update all existing tickets that have no timers set (which is different then having 0 set)

3. Create Rules to Govern Timers

We need to deal with tickets when they are modified. Tickets can be modified in the GUI, by email and by ticket rules. We need to keep track of all of those possibilities. Here are combination of events we need to deal with respect to status and tickets being modified: OTS=On Ticket Save

Case

How Ticket is Updated

What is changed

Evidence of status

When we need to act

What we need to change

Notes

1

Created by User

Anything including status

Contents of ticket only -- no change records yet

As soon as ticket is saved &before other rules

Initialize all relevant fields

-

2

User Save or Email

Anything including status

There will be a change record for status changes

Before other OTS rules change status

All timer related fields

-

3

User Save or Email

Anything but status

There will be a change record but no change record for status change. Current status is accurate

Before other OTS rules change status

All timer related fields

-

4

Scheduled Rule

Anything could change including status

No change record for status change, but current status is accurate

Before any scheduled rules change status

All timer related fields

If we use a rule that runs on the most frequent schedule and has the lowest order then we can "beat"any other rules

5

OTS Rule that fires after case 1,2 or 3

Anything could change including status

No change record for status change, but current status is accurate

Before any OTS rules change status

All timer related fields

-

6

User Save Press

Nothing

No change record, but current status is accurate

Before OTS rules that can run with no changes

All timer related fields

This is a save press with no change. We could ignore these and not update ticket but it would be nice if user had a way to update the timer without waiting for scheduled timers or a change event

We will need a rule for each. We can combine case 3, 5 and 6 into one rule because they all fire at the same time -- "ON TICKET SAVE" AND none of them have a record of a status change but the current status is accurate.

So we need 4 rules to keep track of the timers. We also need a rule for our replacement of the modified field. We can combine that with case 1 since they both run first.

Here are the rules:

Case

Rule Name

1

Adjust custom modified field

2

React to changes that adjust status

3,5,6

Proact to OTS Rules that adjust status

4

Proactively increment the counters

 
 
Part III - The Details of the Rules coming soon
View comments (8)
Showing 11 - 15 of 44 results

Top Contributors

Talk About K1000 Managed Installations (MI)