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.