Trying to create node down alert for condition where 3 or more nodes are in same area based on a custom property such as "City"

I have a custom property of City, I want the alert to trigger when 3 or more devices with the same City value are down. I would like this to be a Node alert , I know I can create groups and alert based on a group, but I think this can be done with a node alert. Help?

Find more posts tagged with

advanced_alerts

alerts_configuration

npm

Alerting

Accepted answers

All comments

vinay.by

You will need a custom swql alert, something like one mentioned below:

SELECT Nodes.NodeID, Nodes.Caption, COUNT(*) AS DownNodesCount
FROM Nodes
WHERE Nodes.Status = 'Down' AND <CUSTOM PROPERTY CHECK>
GROUP BY Nodes.NodeID, Nodes.Caption
HAVING COUNT(*) >= 3

Sriram_1

Have you tried enabling advance options in the alert conditions.

jtp74021

Vinay.by let me clarify further, the Query you have would work if I were creating the alert for specific city and then would have to create it by each city. And apologize for not being more specific in my original ask. I am looking for this alert to trigger when 3 or more locations within the same City (CustomProperty) were down. I do follow where you are going.

jtp74021

This would not trigger with a specific value in the Node City Line. What I want it to do is to compare the City (Value) with 3 nodes or more in that City to be down at the same time to trigger.

shuth

Messy but maybe something like this. It would trigger a node down alert for every node down in a city with more 3 or more nodes down.

If 2 nodes go down in a city then no alert. If 6 nodes go down in a city, you'll get 6 alerts because it's a node alert...

Custom SWQL Alert (for whatever reason I can't insert a code block so the formatting and spacing is a bit scuffed)

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

WHERE Nodes.NodeID IN
(SELECT NodeID FROM Orion.Nodes n
WHERE n.Status = 2
AND n.CustomProperties.City IN
(
SELECT n.CustomProperties.City
FROM Orion.Nodes n
WHERE n.Status = 2
GROUP BY n.CustomProperties.City
HAVING COUNT(*) >=3
)
)

There is probably a neater way to do such a query.

jtp74021

This is really close to what I am looking for, However, it basically would alert for all the nodes that have 3 or more down in a city as one alert. I'm looking for the alert to trigger when 3 devices or more are down in a city that go down at the same time would trigger the alert for just that city and then if another city has the same issue it would then trigger for that city and so on.