Is it possible to generate a report that shows the amount of Unmanaged time for each Node during a month?

pflanz

I need to monitor the amount of Unmanaged time for each of our servers.   I need this on a monthly basis.

Is it possible to generate such a report using Web Based Reporting?

I can run a report that shows how many servers are Unmanaged at any given time, but I can not seem to find a way to show how much unmanaged time actual happens each month for each server

mesverrum

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