Advanced Alert: Custom SQL Query

Question

Hello,

After getting nowhere with support who are refusing to assist with this I was hoping some other users of NPM may be able to assist since "SQL Querys are outside of support scope", even though I dont agree the SQL is at fault.

Basically I am trying to alert for certain IP Addresses where 4 of my 5 custom properties are blank. The Custom Properties being - DEVUATLIVE, KeySystem, Region and Segment)

SELECT Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name

FROM Nodes

WHERE (IP_Address LIKE '%10.120.4.%' OR IP_Address LIKE '%10.218.0.%' OR IP_Address LIKE '%10.218.1.%' OR IP_Address LIKE '%10.218.2.%' OR IP_Address LIKE '%10.218.3.%' OR IP_Address LIKE '%10.218.4.%' OR IP_Address LIKE '%10.218.5.%' OR IP_Address LIKE '%10.218.6.%' OR IP_Address LIKE '%10.218.7.%' OR IP_Address LIKE '%10.218.8.%' OR IP_Address LIKE '%10.218.9.%' OR IP_Address LIKE '%10.218.10.%' OR IP_Address LIKE '%10.218.11.%' OR IP_Address LIKE '%10.218.12.%' OR IP_Address LIKE '%10.218.13.%')

AND (DEVUATLIVE IS NULL OR KeySystem IS NULL OR Region IS NULL OR Segment IS NULL)

Checks every 12hrs and alerts only if condition exists for more than 1hr.

The problem is that when the alert does detect nodes it triggers and I get notified about the missing values. I then set the values and wait for the reset to occur, this doesnt happen; I have to go in manually and clear the alert from Advanced Alert Manager.

I have executed the SQL via MS SQL Management Studio and Solarwinds DB Manager (once the values have been added after the alert was triggered) and it returns 0 rows

If anyone can test a similar query or point out what is wrong please can you do so.

Thanks,

Peter

PeterLyttle · Accepted Answer

Hi Andrew,

Thanks for the reply. If I am understanding correctly, on the "Reset Condition" tab I have tried both options -

Reset when trigger conditions are no longer true

Reset this alert when the following conditions are met:

SELECT Nodes.NodeID as NetObjectID, Nodes.Caption as Name

FROM Nodes

WHERE NOT

(IP_Address LIKE '%10.120.4.%' OR IP_Address LIKE '%10.218.0.%' OR IP_Address LIKE '%10.218.1.%' OR IP_Address LIKE '%10.218.2.%' OR IP_Address LIKE '%10.218.3.%' OR IP_Address LIKE '%10.218.4.%' OR IP_Address LIKE '%10.218.5.%' OR IP_Address LIKE '%10.218.6.%' OR IP_Address LIKE '%10.218.7.%' OR IP_Address LIKE '%10.218.8.%' OR IP_Address LIKE '%10.218.9.%' OR IP_Address LIKE '%10.218.10.%' OR IP_Address LIKE '%10.218.11.%' OR IP_Address LIKE '%10.218.12.%' OR IP_Address LIKE '%10.218.13.%')

AND (DEVUATLIVE IS NOT NULL OR KeySystem IS NOT NULL OR Region IS NOT NULL OR Segment IS NOT NULL)

I have just noticed that when you set a SQL Reset trigger it automatically negates the trigger query (WHERE NOT), I'm going to see if this resolves the problem. (I assume "Reset when trigger conditions are no longer true" also uses this method behind the scenes)

Advanced Alert: Custom SQL Query

Top Replies