Need to trigger an alert when another alert triggers multiple times

hebron1212

Trying to create an alert on this SWQL query, when the number of identical alerts is more than X over 24 hours. The query works fine in SWQL Studio, but I need to join it to another table when creating the alert (as required by the alert config). There do not seem to be any common columns in the Nodes and AlertHistory tables, so I need to find another one.

SELECT COUNT(*) as NumberOfDeauthentications, Message
FROM Orion.AlertHistory
Where Message like '%Max%'
AND
(TimeStamp > (GetUtcDate() - 1))
group by Message

I did a similar alert based on an event, but it does not work for an alert: https://thwack.solarwinds.com/t5/Alert-Lab-Discussions/Is-there-a-way-in-the-alert-to-include-how-many-times-the-alert/m-p/607168#M6414

Any help is appreciated.

mrxinu

Hey @hebron1212! You've got a little overlap going on between the static portion of the query in the top box and what you've provided in the bottom box (they both have SELECT statements).

You need to use the same maneuver (awkward, I know) that you did in the thread you referenced that starts the portion in the box you're working in starting with one or more JOINs to other tables.

This one is a little tricky in that the path from Orion.Nodes to Orion.AlertHistory is not one hop but two. There's no mention of NodeID in the Orion.AlertHistory table directly, so you'll need to go from Orion.Nodes to Orion.AlertObjects finally to Orion.AlertHistory. At that point you can apply the filter you described about the message contents and the age of the alert.

Aside from the multi-table JOIN, you'll also need to apply a condition to get your "more than X" requirement. Because it's an aggregate (COUNT) that's being considered you'll need to use a HAVING clause instead of where you'd typically put a filter on a query (WHERE).

All put together, this should get you what you're looking for. Note that when I'm involving the Orion.AlertObjects table I'm taking care to only consider rows where the EntityType is Orion.Nodes because this alert needs to stick to nodes to trigger correctly.

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
INNER JOIN
(
    SELECT
        Objects.RelatedNodeID AS NodeID
        , COUNT(*) AS TotalCount
    FROM Orion.AlertObjects AS Objects
    INNER JOIN Orion.AlertHistory AS History ON Objects.AlertObjectID = History.AlertObjectID
    WHERE Objects.EntityType = 'Orion.Nodes'
    AND History.TimeStamp > (GETUTCDATE() -1)
    AND History.Message LIKE '%Max%'
    GROUP BY Objects.RelatedNodeID
    HAVING COUNT(*) > 10
) AS DeauthCounts ON Nodes.NodeID = DeauthCounts.NodeID

Note that you'll only put the query starting on line two into the box because that first line is provided for you already as part of the custom SWQL alert. Like this:

INNER JOIN
(
    SELECT
        Objects.RelatedNodeID AS NodeID
        , COUNT(*) AS TotalCount
    FROM Orion.AlertObjects AS Objects
    INNER JOIN Orion.AlertHistory AS History ON Objects.AlertObjectID = History.AlertObjectID
    WHERE Objects.EntityType = 'Orion.Nodes'
    AND History.TimeStamp > (GETUTCDATE() -1)
    AND History.Message LIKE '%Max%'
    GROUP BY Objects.RelatedNodeID
    HAVING COUNT(*) > 10
) AS DeauthCounts ON Nodes.NodeID = DeauthCounts.NodeID