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;