Home Analytics

Average Resolution and Response Time speedometers not working

Ian_StephensonIan_Stephenson Customer IT Monkey ✭

Under the Dashboards -> Incident view, the two speedometers mentioned above do not show anything. However, there is another view that has those values expressed as a number and it works fine. Is there something I have to do to make the speedometers work?

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Ian_Stephenson - It's possible you're looking at 2 different queries. Here is the query that Average Resolution Time is using:

    SELECT ISNULL(AVG(ISNULL(DATEDIFF(minute, Created, ResolvedDate),0)),0) AveResolutionTime   

    FROM [WorkItem] wi    

    LEFT JOIN [Access_CI$User_WorkItem] a ON wi.Id = a.WorkItemId   

    WHERE wi.ClassId='A604B942-4C7B-2FB2-28DC-61DC6F465C68'   

    AND wi.Created >= DATEADD(day, -1, GETUTCDATE())   

    AND wi.ResolvedDate IS NOT NULL   

    AND [email protected]   


    Essentially it's asking for the average resolution time of Resolved work items Created and Resolved in the last 24 hours that I have access to. It's possible that's 0?

  • Ian_StephensonIan_Stephenson Customer IT Monkey ✭

    How can I test it? I create and resolve work items quite a bit throughout the day but the speedometer needle never moves for. I haven't seen it work for any of our users either.

  • Ian_StephensonIan_Stephenson Customer IT Monkey ✭

    Also, is there a way to see the SQL query that is responsible for these gauges?


    Since they are the ones I'm working on, I'd like to see if I can just get any data to come up in SQL when I run the query.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    The query for Average Resolution Time is what I posted earlier. The query for Average Response Time is:

    SELECT ISNULL(AVG(ISNULL(DATEDIFF(minute, Created, FirstResponseDate),0)),0) AveFirstReponseTime   

    FROM [WorkItem] wi    

    LEFT JOIN [Access_CI$User_WorkItem] a ON wi.Id = a.WorkItemId   

    WHERE wi.ClassId='A604B942-4C7B-2FB2-28DC-61DC6F465C68'   

    AND wi.Created >= DATEADD(day, -1, GETUTCDATE())   

    AND wi.FirstResponseDate IS NOT NULL   

    AND [email protected]


    You can set @UserId so you can use the queries in SSMS like this:

    DECLARE @UserId uniqueidentifier 

    SET @UserId = (SELECT Id FROM CI$User WHERE username = '<Your UserName Here>')

  • Ian_StephensonIan_Stephenson Customer IT Monkey ✭

    Thank you! I'll go test it out and see what I can find. I'm just confused because this dashboard is showing a number for both:


    This is under Analytics, Current Incident.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    I didn't supply the entire query above, just the bit I knew was running. The queries in that numeric dashboard are very similar to queries in the radial gauge queries with one major exception. In the queries with the radial gauges the initial IF is IF @UserId IS NULL (which I knew to not be true so I only shared the bit in the ELSE statement) and the rest of the query is the same.

    In the numeric dashboard above, the IF is IF @IsScoped = 0. If you're not scoped on work items (which I'm betting you're not; you can open up the Dev Tools Console in the browser and check by running this command: session.user.Security.IsWorkItemScoped) it doesn't join on the Access_CI$User_WorkItem table and so you end up with a number.

    You could update the query the radial gauges is using. I have attached a query that will do just that. This may work for now, but be aware it will get overwritten when you upgrade the portal.


  • Ian_StephensonIan_Stephenson Customer IT Monkey ✭

    Great I'll give that a try! Do I have to save those in CustomSpace? I don't have the option to edit the radial gauge queries like I do with the other charts.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    That query will edit the ViewPanel definition in the ViewPanel table in the database.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Ian_Stephenson - Here's a video of me walking through my environment to get the queries for each of the dashboards. I hope this helps!

  • Ian_StephensonIan_Stephenson Customer IT Monkey ✭

    @Justin_Workman we still can't get these gauges to work, how do we go about hiding them from the page? I can see the div that defines the row they are in but can't determine what it is actually called so that I can try to hide it.

Sign In or Register to comment.