Need count of active alerts to generate a new alert

Question

I'm trying to create an alert that is generated when the count of other active alerts reaches a certain number. I can get exactly what I need with this SWQL query, but the alert engine requires me to wrap it in a Nodes (or other) query. This then requires multiple joins with other tables, which I have yet to figure out. (Note: I do not want to count the number of alerts per node, just the total number of alerts with the same message.)

SELECT COUNT(*) as AlertsPer, AlertActive.TriggeredMessage AS MessageFROM Orion.AlertActive Where TriggeredMessage like 'The primary or backup link for this site has failed or is degraded.' group by Message HAVING COUNT(*) >= 2Which returns:
Any help is appreciated.

hebron1212 · Answer

Thanks for your quick response. I'll see if I can use groups for this.

The following works for counting alerts by node, so if a single node has 5 different alerts, it creates another alert.

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 not like '%currently has 5 or more active alerts.%' and Message not like '%Custom - Interface Utilization Standard Deviation%'

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

AS a ON a.TheNode=Nodes.NodeID
Where AlertsPer >= 5

But I need a total count of alerts with identical messages. For example, when multiple remote sites have the syslog-based alert "The primary or backup link for this site has failed or is degraded" I can infer that there is an issue with the local sdwan appliance and create an alert for that.

KMSigma.SWI · Answer

I'm not sure the alerting engine has the "logic" to handle alerts about alerts.  I've done similar (not identical) things with groups, but it was very ugly and was years ago.  This feels much more like an Orion "group" alert.  Put the two interfaces into a group and alert when the group itself is down (all members are in a down state).