This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Question on this monthly availability report

So I have been using the following SQL to generate a downtime report for management I have a couple of questions on it.  

1) Can the time be converted from hours to minutes?

2) Is there anyway that I can limit false positives from the SW side.  Let's say that a polling engine goes belly up for a period of 2 hours.  Those servers on that polling engine, despite being up, will have that outage reflected in the report

Any/All input welcome

SELECT n.Caption,
CASE
WHEN AVG(a.Availability) = 100 THEN
'0'
WHEN AVG(a.Availability) = 0 THEN
CONVERT(VARCHAR, DAY(EOMONTH(DATEADD(m, -1, GETDATE())))) + ' Days'
WHEN SUM((100 - a.Availability) * b.Weight) / 100 >= 86400 THEN
CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(11, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) / 86400))
) + ' Days '
+ CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(11, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) % 86400
/ 3600
)
)
) + ' Hours'
WHEN SUM((100 - a.Availability) * b.Weight) / 100 >= 3600
AND SUM((100 - a.Availability) * b.Weight) / 100 < 86400 THEN
CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(5, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) / 3600))
) + ' Hours '
+ CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(5, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) % 3600
/ 60
)
)
) + ' Mins'
ELSE
CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(4, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) / 60))
) + ' Mins '
+ CONVERT(
VARCHAR,
(FLOOR(CONVERT(NUMERIC(4, 0), FLOOR(SUM((100 - a.Availability) * b.Weight) / 100)) % 60))
) + ' Secs'
END AS 'Total Down Time',
AVG(a.Availability) AS Availability
FROM ResponseTime a WITH (NOLOCK),
ResponseTime b WITH (NOLOCK),
Nodes n WITH (NOLOCK)
WHERE a.NodeID = b.NodeID
AND a.DateTime = b.DateTime
AND DATEDIFF(MONTH, a.DateTime, GETDATE()) = 1
AND n.NodeID = a.NodeID
AND n.IsTest = 0
AND n.Vendor IN ('Windows','Linux')
GROUP BY n.Caption
ORDER BY 3;