Hi Reporting World,
I'm trying to make a report to show the down time of devices from the previous month and the time it was down - we have the standard downtime report however that covers interfaces which just won't work.
The expectation is to marry this report up with an availability report to show what was down and why availability isn't 100%.
I have been working on this code however I can't get the right results:
SELECT
NodesData.Caption,
NodesData.IP_Address,
NetObjectDowntime.DateTimeFrom,
(CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, getdate()) / 1440) AS nvarchar(200)) + ':' +
CAST(((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, getdate()) / 60) % 24) AS nvarchar(200)) + ':' +
CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, getdate()) % 60) AS nvarchar(200))) AS OutageInDHM
FROM
NetObjectDowntime INNER JOIN
NodesData
ON NetObjectDowntime.NodeId = NodesData.NodeID INNER JOIN
NodesCustomProperties
ON NetObjectDowntime.NodeId = NodesCustomProperties.NodeID
WHERE
NetObjectDowntime.DateTimeFrom BETWEEN (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, -0, getdate()) , 0)) AND
NetObjectDowntime.EntityType like '%Nodes' AND
NetObjectDowntime.DateTimeUntil IS NOT NULL AND
(CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, NetObjectDowntime.DateTimeUntil) / 1440) AS nvarchar(200)) + ':' +
CAST(((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, NetObjectDowntime.DateTimeUntil) / 60) % 24) AS nvarchar(200)) + ':' +
CAST((DateDiff(MINUTE, NetObjectDowntime.DateTimeFrom, NetObjectDowntime.DateTimeUntil) % 60) AS nvarchar(200))) NOT LIKE '0:0:0%'
ORDER BY
NetObjectDowntime.DateTimeFrom ASC
I'm not particularly good with SQL and have got some help from the SQL guy in my office around the date stuff, still not convinced it's working correctly though.
Any help would be appreciated 
Not sure if this is the right approach however it's the only table with downtime so it seems to be the logical place to pull the results from.
Cheers,
Garreth.
