Custom Alert SQL / SWQL Advenced

Question

SELECT I.FullName, I.InterfaceID FROM [dbo].[Interfaces] I INNER JOIN [dbo].[Nodes] N ON I.NodeID = N.NodeID INNER JOIN [dbo].[NodesCustomProperties] NodeProps ON I.NodeID = NodeProps.NodeID WHERE I.Status = 1 AND I.Caption = 'ge-0/0/3 - MERKEZ' AND N.Caption = 'x.x.1 MERKEZ' AND NodeProps.GECICI_HIZ IS NOT NULL AND NodeProps.GECICI_HIZ > 0 AND EXISTS ( SELECT 1 FROM [dbo].[InterfaceTraffic] ITraffic WHERE ITraffic.InterfaceID = I.InterfaceID AND ITraffic.In_Averagebps > (NodeProps.GECICI_HIZ * 1000000) AND ITraffic.Datetime > DATEADD(minute, -5, GETUTCDATE()) GROUP BY ITraffic.InterfaceID HAVING COUNT(*) >= 1 )

I’m trying to monitor the WAN interface with the caption ge-0/0/3 - MERKEZ on the node 10.x.x.1 MERKEZ.I have a custom property called GECICI_HIZ (stored in the NodesCustomProperties table, I believe) that defines a temporary bandwidth limit (e.g., 15 Mbps). I want the alert to trigger when the interface’s incoming bandwidth (InBps) exceeds this GECICI_HIZ value (converted to bps, so 15 Mbps = 15,000,000 bps) for at least 5 minutes continuously.

brscott · Answer

And now that I have provided a direct answer, I have the doom and gloom.

DISCLAIMER:  I don't do much direct SQL on soalrwinds, so take this with a grain of salt.

Looking at the "exists" you are going to get an alert if it exceeds your limit even once in the last 5 minutes, and not continuously for that 5 minutes.

Unless you changed it, the default polling time is 9 minutes, so for 5 minutes you will get a max of 1 point in time measurement, and for 4 minutes you will get 0, based on your query.

Honestly I recommend that you move the limit value from your custom property to using the interface settings for thresholds.  You have to make sure the "Bandwidth" is set correctly so that the treshold percentages are calculated correctly, but it will seriously make your life much easier than doing custom sql all the time.  Then just use the alert condition builder.

Also instead of SQL I recommend using SWQL.  Yeah, I know it's different and you will get frustrated trying to figure out the difference between SQL and SWQL, but you will be able to get more help in the forums.  And the other benefit is that you don't have to worry about the underlining table changes during an upgrade and your alert breaks without you even knowing.  Again noting my above disclaimer, but the SWQL version of your query would look something like this:

SELECT
    Interfaces.FullName,
    Interfaces.InterfaceID
FROM Orion.NPM.Interfaces Interfaces
WHERE
    Interfaces.Status = 1
    AND Interfaces.Caption = 'ge-0/0/3 - MERKEZ'
    AND Interfaces.Node.Caption = 'x.x.1 MERKEZ'
    AND Interfaces.Node.CustomProperties.GECICI_HIZ IS NOT NULL
    AND Interfaces.Node.CustomProperties.GECICI_HIZ > 0
    AND (
        SELECT count(ITraffic.InterfaceID) as c
            FROM Orion.NPM.InterfaceTraffic ITraffic
            WHERE 
                ITraffic.InterfaceID = Interfaces.InterfaceID
                AND ITraffic.InAveragebps > (Interfaces.Node.CustomProperties.GECICI_HIZ * 1000000)
                AND ITraffic.Datetime > ADDMINUTE(-5, GETUTCDATE())
) > 0

github.com/.../wiki

brscott · Answer

In your "test" sql you have "FROM [dbo].[Interfaces] I", but the base select in the alert that you have to start with is just "From Interfaces" with no alias.

Go Change all of your "I." to "Interfaces." and it should work.