1 Reply Latest reply on Nov 30, 2017 9:52 PM by mesverrum

    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

        • Re: Is it possible to generate a report that shows the amount of Unmanaged time for each Node during a month?
          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