-
Re: Is it possible to generate a report that shows the amount of Unmanaged time for each Node during a month?
mesverrumNov 30, 2017 9:52 PM (in response to pflanz)
It would have to be SQL/SWQL because the web based writer doesn't give you a lot of options when it comes to adding values up, it only lets you do min/max/avg/count in most cases. I use a downtime report that looks at the event history and counts up the difference between the down event and an up event. I could imagine it being possible to change the events that it looks at to rework that report into one that added up all the gaps for a month but it would take some work.
SELECT
-- Device name
StartTime.Nodes.Caption AS [Device]
-- This will generate the status icon to the left of the device name
,'/Orion/images/StatusIcons/Small-' + StartTime.Nodes.StatusIcon AS [_IconFor_Device]
,starttime.nodes.detailsurl as [_linkfor_Device]
-- Down Event time stamp in local time zone
,ToLocal(StartTime.EventTime) AS [Down Event]
-- Up Event time stamp in local time zone
,(SELECT TOP 1
ToLocal(EventTime) AS [EventTime]
FROM Orion.Events AS [EndTime]
WHERE EndTime.EventTime >= StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime
) AS [Up Event]
-- Downtime duration in minutes. Based on either the current time (node is still down) or Up Event time (node is back up)
,CASE
WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]
FROM Orion.Events AS [EndTime]
WHERE EndTime.EventTime >= StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
AND EventTime IS NOT NULL
ORDER BY EndTime.EventTime) IS NULL THEN MINUTEDIFF(StartTime.EventTime, GETUTCDATE())
ELSE MINUTEDIFF(StartTime.EventTime,
(SELECT TOP 1 EventTime
FROM Orion.Events AS [EndTime]
WHERE EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetObjectID = StartTime.NetObjectID
ORDER BY EndTime.EventTime))
END AS [Minutes]
-- This is the table we are querying
FROM Orion.Events StartTime
-- EventType = 1 is to correlate with our 'Down Event' time stamp from line 13 above
WHERE StartTime.EventType = 1
and daydiff(starttime.eventtime,getdate())<7 -- the logic for the timestamp would need to be redone to do this monthly
-- Order our results from the newest to oldest 'Down Event'
ORDER BY [Down Event] DESC