How to reset a SWQL-based alert when number of returned rows is zero

I have the following SWQL alert trigger condition when there are a certain number of alerts on a particular device. I need to create a reset condition so that when the number of alerts on that device go back down to zero, the SWQL alert clears. The query doesn't return a value for zero rows, so I don't know how to reset the alert. This cannot be manually closed by company policy.

Trigger Condition:

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
LEFT JOIN
(SELECT COUNT(*) as AlertsPer, AlertActive.TriggeredMessage AS Message, AlertObjects.EntityCaption, AlertObjects.RelatedNodeID AS TheNode
FROM Orion.AlertObjects
INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID
Where (Message like '%power%' or Message like '%line card%' or Message like '%module%') and Message not like '%1 or more active%'

group by TheNode
HAVING COUNT(*) >= 1)

AS a ON a.TheNode=Nodes.NodeID
Where Nodes.Vendor = 'Cisco' and AlertsPer >= 1

This Reset Condition works with 1 alert but not multiple:

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
Left Join
(SELECT COUNT(*) as AlertsPer, AlertActive.TriggeredMessage AS Message, AlertObjects.EntityCaption, AlertObjects.RelatedNodeID AS TheNode
FROM Orion.AlertObjects
INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID
INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID
Where Message like '%power%' or Message like '%line card%' or Message like '%module%' or Message like '%1 or more active%'

group by TheNode
HAVING COUNT(*) = 1)

AS a ON a.TheNode=Nodes.NodeID
Where Message like '%1 or more active%' and Nodes.Vendor = 'Cisco' and AlertsPer = 1

I've also tried setting the COUNT or AlertsPer to 0, but that doesn't work either.

Find more posts tagged with

Alert

SWQL

Accepted answers

maciej.sandecki

Since the only changing value here is AlertsPer and the alert triggers when AlertsPer >=1, then it should automatically reset when AlertsPer goes to 0 (so the node URI is no longer returned in the set). So isn't the 'Reset this alert when trigger condition is no longer true' working in this case?

All comments

maciej.sandecki

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.
Please try this first in a lab environment, before implementing to your production.

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.

This is a common issue with reset conditions in SWQL alerts. If the condition depends on the presence of date, a node with zero matching alerts will not appear in the result set, that means the reset never happens, because the condition has not been evaluated for the node. There is a way to address this so SolarWinds can evaluate the reset condition properly and that is to explicitly return 0 for nodes with no matching alerts.

Solution: Use `ISNULL()` with `LEFT JOIN` to Handle Zero Alert Rows

Here's how to build a reset condition that resets the alert when the matching alert count drops to zero, by including all Cisco nodes and counting matching alerts (even if zero).

Revised Reset Condition (Works for 0-count Alerts)

<span><span class="hljs-keyword">SELECT</span><br />&nbsp; Nodes.Uri,<br />&nbsp; Nodes.DisplayName<br /><span class="hljs-keyword">FROM</span><br />&nbsp; Orion.Nodes <span class="hljs-keyword">AS</span> Nodes<br /><span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> (<br />    <span class="hljs-keyword">&nbsp; SELECT</span><br />&nbsp; &nbsp; AlertObjects.RelatedNodeID <span class="hljs-keyword">AS</span> TheNode,<br />      <span class="hljs-built_in">&nbsp; &nbsp; COUNT</span>(<span class="hljs-operator">*</span>) <span class="hljs-keyword">AS</span> AlertsPer<br />    <span class="hljs-keyword">&nbsp; FROM</span><br />&nbsp; &nbsp; Orion.AlertObjects<br />      <span class="hljs-keyword">&nbsp; &nbsp; INNER</span> <span class="hljs-keyword">JOIN</span> Orion.AlertActive AlertActive <span class="hljs-keyword">ON</span> AlertObjects.AlertObjectID <span class="hljs-operator">=</span> AlertActive.AlertObjectID<br />    <span class="hljs-keyword">&nbsp; WHERE</span><br />&nbsp; &nbsp; (AlertActive.TriggeredMessage <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%power%&#39;</span><br />       <span class="hljs-keyword">&nbsp; &nbsp; OR</span> AlertActive.TriggeredMessage <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%line card%&#39;</span><br />       <span class="hljs-keyword">&nbsp; &nbsp; OR</span> AlertActive.TriggeredMessage <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%module%&#39;</span>)<br />      <span class="hljs-keyword">&nbsp; &nbsp; AND</span> AlertActive.TriggeredMessage <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%1 or more active%&#39;</span><br />    <span class="hljs-keyword">&nbsp; GROUP</span> <span class="hljs-keyword">BY</span><br />&nbsp; &nbsp; AlertObjects.RelatedNodeID<br />) <span class="hljs-keyword">AS</span> a <span class="hljs-keyword">ON</span> a.TheNode <span class="hljs-operator">=</span> Nodes.NodeID<br /><span class="hljs-keyword">WHERE</span><br />&nbsp; Nodes.Vendor <span class="hljs-operator">=</span> <span class="hljs-string">&#39;Cisco&#39;</span><br />  <span class="hljs-keyword">AND</span> ISNULL(a.AlertsPer, <span class="hljs-number">0</span>) <span class="hljs-operator">=</span> <span class="hljs-number">0</span></span>

Explanation:

LEFT JOIN ensures all Cisco nodes are included even if there are no active alerts.
ISNULL(a.AlertsPer, 0) ensures the condition evaluates to 0 when no alerts exist — enabling the reset.
We’re filtering only nodes with AlertsPer = 0, meaning no matching alerts are active anymore.

Additional Notes:

You don’t need to reference TriggeredMessage again in the outer WHERE clause — the inner query already does the filtering.
This reset logic will now work for any number of alerts, and properly reset once they drop to zero.

Hope this helps.

hebron1212

Setting the reset on the alert that way does work. I was overthinking it. Thanks for your response!