Analytics using Search Query - Time to Resolve or Complete

Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭
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.

Best Answer

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Ninja IT Monkey ✭✭✭✭
    @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_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @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

  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @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_WorkmanJustin_Workman Cireson Support Ninja IT Monkey ✭✭✭✭
    NumberOfDaysToClose should be the difference between CreatedDate and ResolvedDate.
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @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_WorkmanJustin_Workman Cireson Support Ninja IT Monkey ✭✭✭✭
    Change datediff(dd, Created, ResolvedDate) to datediff(HOUR, Created, ResolvedDate)
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @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_WorkmanJustin_Workman Cireson Support Ninja IT Monkey ✭✭✭✭
    Yep.  That should do it.
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @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

Sign In or Register to comment.