Helpdesk 6 Mean Time to Resolution

My manager came to me and said he wants a report of our mean time to resolution (MTR) in the Altiris Helpdesk Solution 6. I told him sure it's possible, anything is possible with the Altiris Management Suite. I looked around the forums and the Internet, but couldn't find anything, so sadly I had to develop it myself.

We had a few problems, one is we have two teams, basically tier 1 and tier 2. We wanted tier 1 to have a MTR less than 1 day and tier 2 to have a MTR less than 5 days. More importantly, we wanted to track how much time tier 1 spends on an incident separate from tier 2. So if tier 1 spends 3 days on an incident, we want that time to be added to tier 1's MTR and then if tier 2 spends another 4 days on it, those 4 days go to tier 2's MTR. We started with comparing create date and last modified date, but that lumps the entire time into the MTR of whoever is assigned the incident.

After looking at the workitem table in the database I started thinking how we could use that information to keep track of what time was spent where. Each time an incident is updated, a line is added to the table. Below is a quick SQL query to see each line of incident "12" in our Helpdesk.

You see there are 5 versions and the latest version has a "1" in the "is_last" column. The assigned_to_worker_id is a number associated with information from the Altiris_Incidents.dbo.worker table. If we do a quick query we can see what 85 and 94 mean (names have been changed):

So bnye was assigned the incident at first and then assigned it to bdole where four more edits were made. We see that the status also changed to 400. Another quick query tells us what that means:

So it was set to resolved.

The point of all this is to assigned the time from the first edit to the second edit to bnye aka worker 85, and all of the rest of the edits to bdole, worker 94. For our purposes, bnye is tier 1, bdole is tier 2.

So, without further ado, here are the queries we used:

Tier 1 Mean Time to Resolution SQL Query

/*The first SELECT statement will divide the SUM of all the hours we pull in the 
SELECT statements below and divide it by the total number of incidents modified
in the last X number of months*/

SELECT
 SUM(IT.TimeSpent1)/(SELECT COUNT(number)
FROM
 Altiris_Incidents.dbo.workitem t1
WHERE
AND
 t1.modified_on >= DATEADD(mm,-3,getdate()) is_last = '1'
AND
AND
 t1.type_lookup_id in ('10') assigned_to_worker_id in ('131','139','188','178','175','172'))
FROM
(
/*The first SELECT statement pulls all of our incidents
that have only one version, it was resolved the first time
it was opened.
*/
SELECT 
DATEDIFF(hour, modified_on,created_on) TimeSpent
FROM Altiris_Incidents.dbo.workitem t1
WHERE t1.status_lookup_id in ('400') --status is "resolved"
AND t1.modified_on >= DATEADD(mm,-3,getdate())--Pulls the last 3 months, change to -4 for four months, -12 for one year, etc.
AND version = '1'
AND is_last = '1'--Version is 1 and it is the only one
AND t1.type_lookup_id in ('10')--Incident is type "incident"
AND assigned_to_worker_id in ('85','92','121')--Our (not real) tier 1 team

UNION ALL --Union the two SELECT Statements so they are the same column

/*The second SELECT statement pulls incidents with multiple versions
The SELECT statement includes a DATEDIFF. DATEDIFF asks the difference of time
between 2 dates. In this statement, it asks the difference of hours between
the modified_on date and the embedded SELECT statement. The embedded SELECT
statement finds the modified_on date of the next version of the incident. So
we compare the modified dates of the two versions.
*/
SELECT DATEDIFF(hour, modified_on,(SELECT modified_on FROM Altiris_Incidents.dbo.workitem t3 WHERE (t1.number = t3.number) AND (t1.version + 1 = t3.version))) TimeSpent
FROM Altiris_Incidents.dbo.workitem t1
WHERE t1.status_lookup_id in ('300')--Only query while the incident was open.
AND t1.modified_on >= DATEADD(mm,-3,getdate())--Pulls the last 3 months, change to -4 for four months, -12 for one year, etc.
AND t1.type_lookup_id in ('10')--Incident is type "incident"
AND assigned_to_worker_id in ('85','92','121')--Our (not real) tier 1 team
) IT

Tier 2 Mean Time to Resolution SQL Query

/*
Our tier 2 team doesn't open incidents, they only work on incidents
assigned to them, so we only need to use the second SELECT statement
of our two tier 1 statements to find information for tier 2. This is the
statement that queries incidents with multiple versions.

For tier 2, we measure in days instead of hours, which is why we have
CAST statements. With using the CAST statements, we find the number of
days with no decimal, aka 3 days. With the CAST statements we get
decimals, aka 3.63 days. Everything else is pretty much the same.
*/
SELECT CAST(AVG(t.TimeSpent) AS Numeric(12,2))
FROM
(
SELECT CAST(DATEDIFF(day, modified_on,(SELECT modified_on FROM Altiris_Incidents.dbo.workitem t3 WHERE (t1.number = t3.number) AND (t1.version + 1 = t3.version))) AS numeric(12,2)) AS TimeSpent
FROM Altiris_Incidents.dbo.workitem t1
WHERE t1.status_lookup_id in ('300')
AND t1.modified_on >= DATEADD(mm,-3,getdate())
AND t1.type_lookup_id in ('10')
AND assigned_to_worker_id in ('110', '114', '117') --our fake tier 2 team
) T

Final Comments

Looking at our example of incident #12, from January 18, 9:20am to January 18, 10:00am would be assigned to tier 1, and from January 18, 10:00am to January 21, 11:26am would be assigned to tier 2.

Obviously this is setup for our needs, if you need to measure in minutes, put "mi" or "n" instead of "hour" in the DATEDIFF part of the query. For a full list of options, check here:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

If you have thoughts of how to improve the query, please comment, we always want to improve accuracy.