SWQL Help – Report on Total Time Nodes Spent in Warning or Critical State (Last 30 Days)

I've been trying to work out a query to list nodes by how long they've been in an alert state over the past 30 calendar days, not just active alerts by age.

Hi all,

I’m looking to create a SWQL-based report that flags systems which tend to remain in a Warning or Critical state for an extended period of time.

The goal is to:

Calculate the total duration in hours that each node has spent in a warning or critical state over the last 30 calendar days
Help identify systems that require deeper analysis or corrective action based on recurring or prolonged alert conditions
Ideally include both resolved and currently active alerts

Any suggestions on how to best approach this in SWQL would be greatly appreciated.

Thanks in advance!

Find more posts tagged with

Accepted answers

All comments

maciej.sandecki

Hi williammy,

Give this a try and let me know.

SELECT n.Caption, n.IPAddress, SUM(n.NodeDowntimeHistory.TotalDurationMin) AS TotalWarningOrDown, COUNT(n.NodeDowntimeHistory.TotalDurationMin) AS NumberOfWarningOrDown
FROM Orion.Nodes n
WHERE (n.NodeDowntimeHistory.State = 2 OR n.NodeDowntimeHistory.State = 3)
    AND n.NodeDowntimeHistory.DateTimeUntilNow >= ADDDAY(-30, GETUTCDATE())
GROUP BY n.IPAddress, n.Caption

williammy

Thank you, this seems to be promosing, I'm now trying to remember how to dig into the alert history for a specific node.

maciej.sandecki

How would you like to have the alert history presented in the same query / report? Could you draft the report layout (columns, data types), so that the query could be adjusted to match your needs?

williammy

I don't have any specific requirements, I'm hoping to run a query against a specific nodem to get an alert of all alerts/status changes it had in the last 30 days. I'm primarily concerned about the name of the event (such as exceeded threshold critical memory usage) and the timestamp of the event.

ronan.tonilon

Disclaimer:
The content posted herein are provided as a suggestion or recommendation to you for your internal use. The information set forth herein may come from third party website or customers. SolarWinds is not liable for any downtime or any issue that may occur if you perform the following suggestions on the link provided. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.

You can open a support ticket to get insights, but please take note of our Working with Support Policy, customizations are not supported especially if this would involved custom scripts or queries, support can assist in a best effort practice since our support team is intended as a break fix support.

SWQL-based report in SolarWinds to identify systems that have spent significant time in a Warning or Critical state over the past 30 days. This approach helps pinpoint nodes requiring further analysis or corrective action due to recurring or prolonged alert conditions.

The objective of this suggestion:

Calculate: Total duration (in hours) each node has been in a Warning or Critical state over the last 30 days.
Include: Both resolved and currently active alerts.
Identify: Systems with frequent or prolonged alert conditions.

SWQL Query

Here's a SWQL query that achieves this:

<span><span class="hljs-keyword">SELECT</span><br />&nbsp; n.Caption <span class="hljs-keyword">AS</span> [Node Name],<br />&nbsp; n.IPAddress <span class="hljs-keyword">AS</span> [IP Address],<br />&nbsp; ROUND(<span class="hljs-built_in">SUM</span>(DATEDIFF(<span class="hljs-keyword">MINUTE</span>, ah.TimeStamp, ISNULL(ah.ResetTime, GETUTCDATE()))) <span class="hljs-operator">/</span> <span class="hljs-number">60.0</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">AS</span> [Total Alert Duration (Hours)],<br />  <span class="hljs-built_in">&nbsp; COUNT</span>(ah.AlertHistoryID) <span class="hljs-keyword">AS</span> [Number <span class="hljs-keyword">of</span> Alerts]<br /><span class="hljs-keyword">FROM</span><br />&nbsp; Orion.AlertHistory ah<br /><span class="hljs-keyword">JOIN</span><br />&nbsp; Orion.AlertObjects ao <span class="hljs-keyword">ON</span> ah.AlertObjectID <span class="hljs-operator">=</span> ao.AlertObjectID<br /><span class="hljs-keyword">JOIN</span><br />&nbsp; Orion.Nodes n <span class="hljs-keyword">ON</span> ao.EntityID <span class="hljs-operator">=</span> n.NodeID<br /><span class="hljs-keyword">WHERE</span><br />&nbsp; ah.EventType <span class="hljs-operator">=</span> <span class="hljs-number">0</span> <span class="hljs-comment">-- Alert Triggered</span><br />  <span class="hljs-keyword">&nbsp; AND</span> ah.TimeStamp <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">DAY</span>, <span class="hljs-number">-30</span>, GETUTCDATE())<br />  <span class="hljs-keyword">&nbsp; AND</span> ao.EntityType <span class="hljs-operator">=</span> <span class="hljs-string">&#39;Orion.Nodes&#39;</span><br /><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span><br />&nbsp; n.Caption, n.IPAddress<br /><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span><br />&nbsp; [Total Alert Duration (Hours)] <span class="hljs-keyword">DESC</span></span>

Note the below:

ah.EventType = 0: Filters for alert trigger events.
ah.TimeStamp >= DATEADD(DAY, -30, GETUTCDATE()): Considers alerts from the last 30 days.
ISNULL(ah.ResetTime, GETUTCDATE()): Uses the current time for active alerts without a reset time.
ROUND(SUM(...), 2): Calculates total alert duration in hours, rounded to two decimal places.

Expected report Output

The query will produce a report with the following columns:

Node Name: Name of the node.
IP Address: IP address of the node.
Total Alert Duration (Hours): Total time the node has been in a Warning or Critical state over the past 30 days.
Number of Alerts: Count of alert instances during this period.thwack.solarwinds.comdocumentation.solarwinds.com+1solarwindscore.my.site.com+1

How it will look like

To enhance visibility:thwack.solarwinds.com+1documentation.solarwinds.com+1

Create a Custom Report: Use the SolarWinds Report Writer or the web-based Report Manager to schedule and distribute this report.
Dashboard Integration: Incorporate the report into a dashboard for real-time monitoring.
Set Thresholds: Highlight nodes exceeding specific alert duration thresholds to prioritize remediation efforts.

Customization Tips

Filter by Severity: If you want to focus solely on Critical alerts, add a condition to filter by severity.
Adjust Time Frame: Modify the DATEADD function to change the reporting period.
Include Additional Details: Join other tables to incorporate more node information, such as location or device type.

This approach should provide a comprehensive view of nodes with prolonged alert conditions, and help facilitate proactive maintenance and resolution of the issues.

Hope this helps.

williammy

Can this be ran within SWQL Studio? I'm getting an error that ao.EntityID isn't available

maciej.sandecki

This reply was written using ChatGPT and the query has multiple bugs (e.g. there is no DATEDIFF function in SWQL, AlertHistory entity doesn't have ResetTime column and AlertObjects doesn't have EntityID column).