/build/static/layout/Breadcrumb_cap_w.png

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

Comments

  • When is Part III coming? I don't enough SQL to create the rules based on this post. Anxiously waiting. - lmland 11 years ago
  • xmas holidays recovery and all that you know :) posting today! - GillySpy 11 years ago
    • Hi, Gillyspy, I'm just reading this essay, it's quite useful. However, I do have a question, hopefully you can help answer that!
      I really don't understand case 5. If OTS happens, why there's no change record for field change? Not all field change be recorded? Can you tell me in what situation field change will not be recorded? - zhoda02 10 years ago
  • GillySpy,

    Great series, my department is looking to use this to account for the lapses in true SLA times held in Kace tickets.

    You have the Ticket Layout screenshot describing what custom fields you set to a label and their Requirement field values, but how did you define their data types in the Custom Fields table? I read into post three to get an idea and heres what I've inferred, please correct me if I'm wrong.

    CUSTOM_12 or "Modified" (FieldType: Timestamp SelectValues: __blank__)
    CUSTOM_13 or "Total Hours Stalled" (FieldType: Number SelectValues: __blank__)
    CUSTOM_14 or "Total Hours Closed" (FieldType: Number SelectValues: __blank__)
    CUSTOM_15 or "Counter Last Updated" (FieldType: Timestamp SelectValues: __blank__) - psunderhaus 11 years ago
  • Hey, section 2 lists 3 possible ways to update existing tickets with data. I'm happy to either modify the SQL to update tickets after a certain number or run the rule to "Populate Existing Tickets with Baseline Data". Question is how do you do either? Can someone tell me which rules to edit and what to edit them with or alternatively where to find this rule "Populate Existing Tickets with Baseline Data" or create it?
    Thanks. - twit 11 years ago
  • to populate tickets with baseline data you could do something like in a rule:
    select query: select 1

    update query:
    update HD_TICKET as T
    set
    T.CUSTOM_FIELD_VALUE11 = NOW(), /* this is the new modified time field */
    T.CUSTOM_FIELD_VALUE12 = 0, /* total stalled */
    T.CUSTOM_FIELD_VALUE13 = 0, /* total opened */
    T.CUSTOM_FIELD_VALUE14 = NOW() /* time counter last updated */
    WHERE
    T.ID < 1 /* whatever numbers in the past you want to set baseline data for */ - GillySpy 11 years ago
  • Thanks for the reply Gilly but you're speaking to someone who's SQL knowledge is very limited. When you say the select query is select 1 I don't really know what that means? Entering that into the select query had no effect, then trying to figure it out myself almost left the Kbox unresponsive haha. If you have time, telling me exactly what to enter into each field as you did on your original posts would really help. Thanks. - twit 11 years ago
  • sorry, i should have been more clear. I've given you all the SQL simply:
    1. go to a ticket rule and create a new one
    2. in the select query field use "select 1"
    3. update query as i've written
    4. other fields as default
    5. click "run now"
    6. keep the rule disabled because running it twice can't hurt, but won't help either. - GillySpy 11 years ago
  • Once again, thanks. This worked after changing the < toa > in WHERE
    T.ID < 1 /* whatever numbers in the past you want to set baseline data for */
    However it reset all the total hours opened counters to 0 (ZERO). - twit 11 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ