SWQL Script

gadbekr

I have been asked to create a report to show when a particular device goes down for 60 mins or longer and to have a daily report sent out for the previous day only.  Can anyone assist with a SWQL script for this?  I have tried several, but cant find any one that works for what we need.  

thiesaj

I played with this concept quite a bit today, and here is what I came up with:

Many SWQL attempts fail for this type of report because SWQL does not support datetime arithmetic or functions like DATEDIFF or TOTALSECONDS. If someone knows otherwise, please chime in, as I can never really seem to get it to work.

---

This PowerShell script generates a daily report of network devices that experienced outages of 60 minutes or longer in SolarWinds. It queries Node Down and Node Up events from the previous day using the SolarWinds SWIS API and produces a CSV report suitable for email distribution or archival purposes.

Features

• Pulls Node Down (EventType=1) and Node Up (EventType=5) events for the previous day.

• Calculates duration in minutes between Down and Up events.

• Filters to only include outages ≥60 minutes.

• Generates a CSV report in a designated folder (C:\Reports\Outages_YYYYMMDD.csv).

• Displays a summary table in the PowerShell console for quick reference.

• Fully commented for clarity and easy customization.

---

Requirements

• PowerShell 5.1+

• SwisPowerShell module installed (part of SolarWinds Orion SDK)

• Access to a SolarWinds server with credentials that can query Orion.Events and Orion.Nodes.

• Permissions to write CSV files to the designated output folder.

---

How It Works

1. Connects to the SolarWinds server using SWIS.

2. Defines the previous day as the reporting period.

3. Pulls Node Down and Up events for that day.

4. Groups events by node, calculates downtime, and filters for ≥60 minutes.

5. Retrieves the node’s caption for readability.

6. Exports the report to CSV and optionally prints it to the console.

---

Usage

1. Update the script with your SolarWinds server hostname and credentials:
   

   $hostname = "YOUR_SOLARWINDS_SERVER"
   $username = "YOUR_USERNAME"
   $password = "YOUR_PASSWORD"
   

2. Set the folder where reports should be saved (default: C:\Reports).

3. Run the script manually or schedule it in Windows Task Scheduler to run daily after midnight.

4. The generated CSV can be attached to emails or uploaded to a shared location for team visibility.

---

Notes

• This script only includes outages that have a matching Node Up event. Nodes that remain down at the end of the day will not appear unless additional logic is added.

• It can be easily extended to:

  • Include devices still down at the end of the day.

  • Customize the minimum outage duration.

  • Integrate with email alerts for automatic distribution.

---

# -------------------------
# Daily SolarWinds Outage Report (≥60 minutes)
# -------------------------

# Load SolarWinds PowerShell module
Import-Module SwisPowerShell

# -------------------------
# 1. Connect to SolarWinds
# -------------------------
$hostname = "YOUR_SOLARWINDS_SERVER"   # e.g., "solarwinds.company.local"
$username = "YOUR_USERNAME"            # e.g., "domain\admin"
$password = "YOUR_PASSWORD" | ConvertTo-SecureString -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($username, $password)

# Establish SWIS connection
$swis = Connect-Swis -Hostname $hostname -Credential $cred

# -------------------------
# 2. Define time range: previous day
# -------------------------
$today = Get-Date -Hour 0 -Minute 0 -Second 0
$yesterdayStart = $today.AddDays(-1)
$yesterdayEnd   = $today

# -------------------------
# 3. Query Node Down and Up events for yesterday
# -------------------------
$query = @"
SELECT EventID, NetworkNode, EventType, EventTime
FROM Orion.Events
WHERE EventType IN (1,5)
  AND EventTime >= '$($yesterdayStart.ToString("yyyy-MM-ddTHH:mm:ss"))'
  AND EventTime < '$($yesterdayEnd.ToString("yyyy-MM-ddTHH:mm:ss"))'
ORDER BY NetworkNode, EventTime


$events = Get-SwisData -SwisConnection $swis -Query $query

# -------------------------
# 4. Group events by node and calculate downtime
# -------------------------
$report = @()

$events | Group-Object NetworkNode | ForEach-Object {
    $nodeID = $_.Name
    $nodeEvents = $_.Group | Sort-Object EventTime
    $downTime = $null

    foreach ($ev in $nodeEvents) {
        if ($ev.EventType -eq 1) {
            # Node went down
            $downTime = $ev.EventTime
        }
        elseif ($ev.EventType -eq 5 -and $downTime -ne $null) {
            # Node came back up
            $upTime = $ev.EventTime
            $minutesDown = ($upTime - $downTime).TotalMinutes

            if ($minutesDown -ge 60) {
                # Retrieve node caption safely
                $captionQuery = "SELECT Caption FROM Orion.Nodes WHERE NodeID = $nodeID"
                $nodeCaption = (Get-SwisData -SwisConnection $swis -Query $captionQuery).Caption

                $report += [PSCustomObject]@{
                    NodeName     = $nodeCaption
                    NodeID       = $nodeID
                    DownTime     = $downTime
                    UpTime       = $upTime
                    MinutesDown  = [math]::Round($minutesDown,0)
                }
            }

            # Reset downTime
            $downTime = $null
        }
    }
}

# -------------------------
# 5. Output report
# -------------------------
# Ensure folder exists
$csvFolder = "C:\Reports"
if (!(Test-Path -Path $csvFolder)) { New-Item -ItemType Directory -Path $csvFolder | Out-Null }

$csvPath = Join-Path $csvFolder "Outages_$($yesterdayStart.ToString('yyyyMMdd')).csv"

$report | Export-Csv -Path $csvPath -NoTypeInformation

Write-Output "Report generated: $csvPath"

# Optional: display in console
$report | Format-Table -AutoSize

If you have a down node in excess of 60 minutes, a message like this will be printed in your Powershell Window:

What the Report location should look like: