Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Conversion from Report Writer to SQL or SWQL for Downtime Totals Report

Chris-S

This page: Custom query to create a node downtime report (site.com)

... is written for Report Writer, which is no longer used in Orion.

Is there an updated version of this, either in SQL or SWQL? I'm after the equivalent.

Find more posts tagged with

Accepted answers

All comments

stuartd

Just use the code block on the page you linked.

We are running 2023.4.2 and I've just tried it in the Database Manager and it runs just fine once I edited out the section: AND NodeID = '1'

So adding that code block to a Custom Report using the 'Advanced Database Query' option will allow you to directly copy/paste that code in "as is" except for editing the line above to remove (to report on all nodes) or adjust to suit the node you are interested in.

Oh, and be sure to click on the 'Query Type' radio button for SQL.

KMSigma.SWI

Still worth the challenge of converting to SWQL so you can take advantage of the additional features (IMHO).

marcrobinson

SWQL?? hmmm this looks like it works. Gah, easier to start fresh with all the embedded sql statements.

This seems to work. Please try it and let me know.

SELECT
 N.StatusLED,
 N.Caption,
 N.NodeID,
 StartTime.Message,
 StartTime.EventTime AS DownEventTime,
     (
          SELECT TOP 1 EndTimeTable.EventTime
          FROM Orion.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,
MINUTEDIFF(StartTime.EventTime,
            (
               SELECT TOP 1 endtime.EventTime FROM Orion.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 Orion.Events as StartTime
INNER JOIN orion.Nodes as N ON StartTime.NetworkNode = N.NodeID
WHERE (StartTime.EventType = 1) --AND N.NodeID = '1' used to filter by nodeid

and StartTime.EventTime IS NOT NULL
ORDER BY N.Caption ASC, DownEventTime DESC

marcrobinson

honestly starting fresh in swql might have been MUCH easier.

stuartd

I'll leave that to someone far better versed in SWQL than myself...