-
Re: If-Then-Else Alerting
dhanson Jun 24, 2015 5:36 PM (in response to jasadelll)You can create a SQL alert like below. This one, as configured, would trigger if any one node had a status other than 1 (1 is "up").
By the way, the AND statement, that entire row is optional. If you only want to alert on specific locations, and not apply these rules to others, you'd have to define this AND statement. Otherwise, you can remove that whole line, and it'll still function. To further clarify, If site "NOP" does not get the same treatment as sites "ABC", "XYZ" or "NMO", then the AND statement is required. If "NOP" (and all other locations) are treated identically, then remove the entire line that begins with AND.
SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n
WHERE n.Status NOT LIKE '1'
AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'
GROUP BY n.Location
HAVING count(n.Location) = 1
This would satisfy your 2-4 rule.
SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n
WHERE n.Status NOT LIKE '1'
AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'
GROUP BY n.Location
HAVING count(n.Location) > 1 AND count(n.Location) < 4
And here's your 5 or more.
SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n
WHERE n.Status NOT LIKE '1'
AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'
GROUP BY n.Location
HAVING count(n.Location) > 4
You might still have situations where all 3 alerts will trigger, should a cascading failure occur at a site. If, within the same polling cycle, multiple devices are declared down, this would work for you as you intend.
I'm sure the SQL Guru's out there can tell you a million ways to optimize this (or will encourage you to simply make this into a single alert, because why the hell would you need this?), but this should get you started.
-HTH!