Analytics using Search Query - Time to Resolve or Complete

Karen_Bruster1

I was wondering if there is a way to get the time it took an analyst to resolve or complete a ticket using the Search query as I have no idea how to do it using SQL.

Justin_Workman

Now we're getting a little bit more complicated  To get the Support Group, we have to join the DisplayString table.  I went ahead and added AssignedUser too:

SELECT WorkItemId, TItle, AssignedUser,

ds.DisplayString AS 'SupportGroup', datediff(dd, Created, ResolvedDate) AS NumberOfDaysToClose 

FROM WorkItem

LEFT JOIN DisplayString ds ON 

ds.ElementID = Workitem.TierId AND ds.LocaleID = 'enu'

WHERE ResolvedDate IS NOT NULL

AND ds.DisplayString = 'Tier 1'

You can change 'Tier 1' to your support group your looking for.

Justin_Workman

@Karen_Bruster1
You can't get a number of days to resolve from the Search, but here's a quick SQL query that I think gets what you're after:

SELECT WorkItemId, TItle, Datediff(dd, Created, ResolvedDate) AS NumberOfDaysToClose 

FROM WorkItem

WHERE ResolvedDate IS NOT NULL

I'm running this against ServiceManagement.

Karen_Bruster1

@Justin_Workman Thank you for the query.

How would you target a specific support group?  Could you use the following and statement with the WHERE?

AND SupportGroup = "HelpDesk" ;

Karen

Justin_Workman

Now we're getting a little bit more complicated  To get the Support Group, we have to join the DisplayString table.  I went ahead and added AssignedUser too:

SELECT WorkItemId, TItle, AssignedUser,

ds.DisplayString AS 'SupportGroup', datediff(dd, Created, ResolvedDate) AS NumberOfDaysToClose 

FROM WorkItem

LEFT JOIN DisplayString ds ON 

ds.ElementID = Workitem.TierId AND ds.LocaleID = 'enu'

WHERE ResolvedDate IS NOT NULL

AND ds.DisplayString = 'Tier 1'

You can change 'Tier 1' to your support group your looking for.

Karen_Bruster1

@Justin_Workman this is great it is showing the total number of tickets but its not giving an amount of time it took to resolve/complete. Just the number of tickets.

Justin_Workman

NumberOfDaysToClose should be the difference between CreatedDate and ResolvedDate.

Karen_Bruster1

@Justin_Workman Yes it does show that amount of days but I was wondering if there was a way to show actual time as in 1 hour or 2 hours?

Justin_Workman

Change datediff(dd, Created, ResolvedDate) to datediff(HOUR, Created, ResolvedDate)

Karen_Bruster1

@Justin_Workman so it should look like this

added AssignedUser too:

SELECT WorkItemId, TItle, AssignedUser,

ds.DisplayString AS 'SupportGroup', datediff(hour, Created, ResolvedDate) AS NumberofHoursToClose 

FROM WorkItem

LEFT JOIN DisplayString ds ON 

ds.ElementID = Workitem.TierId AND ds.LocaleID = 'enu'

WHERE ResolvedDate IS NOT NULL

AND ds.DisplayString = 'Tier 1'

Justin_Workman

Yep.  That should do it.

Karen_Bruster1

Karen_Bruster1 said:

@Justin_Workman so it should look like this

added AssignedUser too:

SELECT WorkItemId, TItle, AssignedUser,

ds.DisplayString AS 'SupportGroup', datediff(hour, Created, ResolvedDate) AS NumberofHoursToClose 

FROM WorkItem

LEFT JOIN DisplayString ds ON 

ds.ElementID = Workitem.TierId AND ds.LocaleID = 'enu'

WHERE ResolvedDate IS NOT NULL

AND ds.DisplayString = 'Tier 1'

@Justin_Workman Thank you for your assistance this works great. Have a good weekend!

Karen