Availability report problems

When I create a report within Orion Report Writer 8.5 to show the average availability for nodes, it brings back the expected result as a percentage for each node. i.e;

node 1 = 100%

node 2 = 100%

node 3 = 90%

My problem is that as soon as I try and filter it to show only the nodes that have less than 100% average availability, using the following filter "Records where Availability is less than 100" its returns the following;

node 3 = 0.00%

It correctly shows that node 3 is the only node that has less than 100% average availability, but it no longer displays its average percentage, which I need it to show.

Has anyone else had this problem or know of a work around to get it to work?

Find more posts tagged with

filter

report_writer

average_availability

Accepted answers

r0berth1

Ok, Try this.

Select * from (

SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth,
Nodes.Caption AS NodeName,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time,
MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39963 AND 39992.9999884259 )
AND
(
(Nodes.DeviceUsage = 'CORE')
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption

ORDER BY SummaryMonth ASC, 4 ASC) AS X where AVERAGE_of_Availability < 100;

All comments

r0berth1

I had a silimar problem a while back. Can you post the SQL code from the report so i can work on a fix for you?

r0berth1

OK, let me correct my earlier statement. I am still having the problem. When i add the filter "availability not equal to 100" a branch that had 97% availability before the filter, now shows 14.2% availability with the filter applied. I am using Orion 9.5 SP2 with all hotfixes applied.

cstaniforth

Glad its not just me being dumb as a box of rocks, here is the SQL code for the report;

SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
Nodes.Caption AS NodeName,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
AVG(ResponseTime.AvgResponseTime) AS AVERAGE_of_Average_Response_Time,
MIN(ResponseTime.MinResponseTime) AS MIN_of_Minimum_Response_Time,
MAX(ResponseTime.MaxResponseTime) AS MAX_of_Peak_Response_Time

FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

WHERE
( DateTime BETWEEN 39963 AND 39992.9999884259 )
AND
(
(Nodes.DeviceUsage = 'CORE') AND
(ResponseTime.Availability < 100)
)

GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption

ORDER BY SummaryMonth ASC, 3 ASC, 4 DESC

FormerMember

I think I know what's going on here. You're doing an average of the availablility's but you're filtering out any of the availability values of 100 from that average. So you only end up averaging the values that were less than 100%.

My SQL is a little rusty(and I don't have a working DB at the moment), but after discussion here with a couple folks, we think you might be able to change "ResponseTime.Availability<100" to "AVERAGE_of_Availability<100" and it might work. If I get a working DB going before you get a chance to try it, I'll let you know. If you get a chance to try it first, let me know how it goes.

r0berth1

I have a working DB, i will test it and post the results.

r0berth1

That didn't work. It is looking for a Column name and if you enter (AVG(ResponseTime.Availability) < 100) under the WHERE statement it throws an error.