Node Down Time

All,  I am looking for some SWQL/SQL code that can pull out the following information from the database. 

Columns Include: 

  • Device Name
  • IP Address
  • Time Down
  • Time Up 
  • Minutes Node was down
Parents Reply Children
  • If you are trying to use SQL... try this...

    SELECT * from (
    
    SELECT
    
    StartTime.EventTime AS DownEventTime,
    
    (SELECT TOP 1
    
    EventTime
    
    FROM Events AS Endtime
    
    WHERE EndTime.EventTime >= StartTime.EventTime
    
    AND EndTime.EventType = 5
    
    AND EndTime.NetObjectType = 'N'
    
    AND EndTime.NetworkNode = StartTime.NetworkNode
    
    AND EventTime IS NOT NULL
    
    ORDER BY EndTime.EventTime) AS UpEventTime,
    
    Nodes.Caption, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,(SELECT TOP 1 EventTime FROM Events AS Endtime
    
    WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
    
    AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
    
    FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
    
    WHERE (StartTime.EventType = 1)
    
    ) as uptimetable
    
    WHERE outageDurationInMinutes IS NOT NULL
    ORDER By DownEventTime DESC

  • Thanks John its working,

    But somehow its not taking all the down event against a device. Eg. there is a device for which I can see 3 down/up event in web console events tab for last month but SQL output shows  only first two down/up even pair.

    I am not sure why this behavior, please suggest. 

  • Trying to modify it to group by Device and Order by Down Event in descending order but getting an error.