Custom query to create a node downtime report

This article provides an example of a custom SQL report that shows the outage duration for each node, including when and how long it was down. You can also include a filter to show only specific nodes.

You can run this query either from your Orion Report Writer or from your Orion server.

1. Log in to Orion Server with an admin account.
2. Go to Start > All Programs > SolarWinds Orion > Alerting, Reporting, and Mapping > Report Writer.
3. Click New and choose Advanced SQL as the type of report.
4. Run the script below:

-- Scripts are not supported under any SolarWinds support program or service. 
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further 
-- disclaims all warranties including, without limitation, any implied warranties 
-- of merchantability or of fitness for a particular purpose. The risk arising 
-- out of the use or performance of the scripts and documentation stays with you. 
-- In no event shall SolarWinds or anyone else involved in the creation, 
-- production, or delivery of the scripts be liable for any damages whatsoever 
-- (including, without limitation, damages for loss of business profits, business 
-- interruption, loss of business information, or other pecuniary loss) arising 
-- out of the use of or inability to use the scripts or documentation.

SELECT * FROM (

 SELECT
 Nodes.StatusLED,
 Nodes.Caption,
 Nodes.NodeID,
 StartTime.Message,
 StartTime.EventTime AS DownEventTime,
 (
  SELECT TOP 1 EventTime
  FROM Events AS EndTimeTable
  where EndTimeTable.EventTime >= StartTime.EventTime
   AND EndTimeTable.EventType = 5
   AND EndTimeTable.NetObjectType = 'N'
   AND EndTimeTable.NetworkNode = StartTime.NetworkNode
   AND EventTime IS NOT NULL
  ORDER BY EndTimeTable.EventTime
 ) AS UpEventTime,
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) AND NodeID = '1'
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC, DownEventTime DESC

Note: The highlighted part can be changed from set to custom properties depending on your needs.