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
  • You can use this query as an SWQL report or as a Query Widget. It is excellent as a widget as it includes hyperlinks. Credit to the original creator for adding all the comments!

    select n.caption as [Device]
    -- shows the current status icon
    , '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
    -- makes a clickable link to the node details
    , n.DetailsUrl as [_linkfor_Device]
    --Show Mute Node Down Custom Property
    , n.customProperties.IgnoreNodeDown
    -- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
    , isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
    -- shows the timestamp of the up event, unless the object is still down
    , isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]
    -- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and 
    , isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETDATE())),99999) as Minutes
    ,n.Vendor
    from orion.nodes n
    left join (SELECT    
     -- Device nodeid used for our join   
     StartTime.Nodes.NodeID     
    
     -- 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]    
    -- picks the first up event that is newer than the down event for this node
     WHERE EndTime.EventTime >= StartTime.EventTime   
    -- EventType 5 is a node up 
     AND EndTime.EventType = 5    
     AND EndTime.NetObjectID = StartTime.NetObjectID    
     AND EventTime IS NOT NULL 
      
     ORDER BY EndTime.EventTime    
     ) AS [Up Event]    
      
    -- This is the table we are querying    
    FROM Orion.Events StartTime    
      
    -- EventType 1 is a node down
    WHERE StartTime.EventType = 1    
        
    ) t2 on n.NodeID = t2.nodeid
    
    
    -- this is how I catch nodes that are down but have aged out of the events table
    where (n.status = 2 or t2.nodeid is not null)
    
    -- If you want to filter the results to only show outages of a minimum duration uncomment the below line
    --and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) >  60
    
    
    -- if you want to use this query in a search box of the Custom Query resource uncomment the below line
    --and n.Caption like '%${SEARCH_STRING}%'
    --Filter on Vendor
    ---and n.Vendor like '%Windows%'
    
    order by t2.[down event] desc

  • Hi,

    I am trying to use above sql query but its giving me an error as Invalid column name 'Nodes'. at StartTime.Nodes.NodeID

    Please suggest

  • If you would, please give us more details on what you are trying to do?  Maybe you can post your exact code??

  • Also, the above query is not for SQL.. it is for SWQL

  • Thanks John,

    I am using the above mentioned SWQL script to generate report. But its giving me an error Invalid column name 'Nodes'. at StartTime.Nodes.NodeID line in the script.

    Am I missing anything here?

  • there are actually two different scripts above...  which are you using? The one that I posted? or the one that sten posted?  can you post the exact script you are trying to use?

  • Are you trying to run this query in SWQL Studio?

  • When I try to run his Query in SWQL Studio I get an error 

Reply Children