Average Resolution and Response Time speedometers not working
Ian_Stephenson
Customer IT Monkey ✭
in Analytics
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?
0
Answers
@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 a.UserId=@UserId
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?
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.
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.
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 a.UserId=@UserId
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>')
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.
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.
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.
That query will edit the ViewPanel definition in the ViewPanel table in the database.
@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!
@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.
@Ian_Stephenson - Try adding this to your custom.js in CustomSpace:
if(window.location.href.indexOf('c40cec6f-36f2-467e-8b22-de8869ed9600') !==-1){
setTimeout(function(){
$('.panel-heading:contains("Average Resolution Time")').parent().parent().parent().hide();
}, 2500);
}
That did the trick, thanks!