Alert using custom SWQL - works in SWQL Studio, adding the alert shows error in query box

Example from SWQL Studio:

Example from creation of alert:

Working with SWQL Alerts requires some adjustment.

When you select 'Custom SWQL Alert' there is a section (#1 below) that hard codes your SELECT statement for you. This statement is hard coded for all types that can be selected from the Nodes dropdown directly above it.

That means you have to modify your query to match the alias which is shown. Once the query is updated, you paste ONLY the JOINS and other parts of the query into the open section below it (#2)

So if I were to take a shot at rewriting your query, it would look something like this. I don't have the exact scenario in your environment, so the query might still require some slight modifications.

--The SELECT statement is hardcoded into the alert
SELECT 
     Nodes.Uri
    ,Nodes.DisplayName 
FROM Orion.Nodes AS Nodes

--Paste everything below this line into the Custom SWQL Alert
LEFT JOIN Orion.Routing.Neighbors AS Neighbors ON Neighbors.NodeID = Nodes.NodeID
WHERE 1=1
    AND Neighbors.ProtocolName = 'Cisco EIGRP'
    AND Neighbors.IsDeleted = 0
    AND Neighbors.ProtocolStatusDescription = 'Established'
GROUP BY Nodes.DisplayName, Nodes.NodeID
HAVING COUNT(*) IN (1, 3)
ORDER BY Nodes.DisplayName

Thank you, Chad.   So, this works!  Before I read your response, I had enhanced my original query (and tested it successfully in SWQL Studio) to only check devices in a certain group (container), which is what we want, and it looks like this:

SELECT 
    cm.Name
FROM 
    (
        SELECT 
            c.Caption
        FROM 
            Orion.Routing.Neighbors AS n
        LEFT JOIN 
            Orion.NPM.Nodes AS d ON n.NodeID = d.ID
        LEFT JOIN 
            Cortex.Orion.Node AS c ON n.NodeID = c.NodeID
        WHERE 
            n.ProtocolName = 'Cisco EIGRP'
            AND n.IsDeleted = 0
            AND n.ProtocolStatusDescription = 'Established'
        GROUP BY 
            c.Caption
        HAVING 
            COUNT(*) IN (1, 3)
    ) AS nc
INNER JOIN 
    (
        SELECT 
            cm.Name
        FROM 
            Orion.ContainerMembers AS cm
        LEFT JOIN 
            Orion.Container AS c ON cm.ContainerID = c.ContainerID
        WHERE 
            cm.ContainerID = 22
    ) AS cm
ON 
    cm.Name = nc.Caption
ORDER BY 
    cm.Name

Because you say that the select statement has to be in the box above, I'm unsure how a nested query like this will work, and I am not sure how to make it into a single SELECT.

You can use nested queries in alerts despite the fact they're not supported. You probably want to Join to the subquery, eg

[Forced top line] Select x.ID FROM  TABLE as x

[Your code] LEFT JOIN (

Select problems from y

) on x.ID = y.ID

Use a RIGHT join is instead you're selecting OKs from y

There are definitely easier ways to to a routing change alert if that's the end goal - There's OOTBs for that I believe

Adam:  The out of the box alerts for neighbors work for fine with BGP, they don't work for EIGRP.   There are long threads in my history here on this topic.  Hence, this kind of custom query.

I'm still trying to figure out how to transform the query I posted above into something that will work in an alert.

Does Chad's not do what you're after? What's missing/failing/etc?

Hi, Adam.  Chad's query does work.  However, it's missing something I added, which is to select only members of a certain group (container)  (the post right after Chad's response, thanks Chad!).  I got the new query to work in SWQL studio but I'm uncertain how to re-organize it to make it work as an alert.

Something like

--The SELECT statement is hardcoded into the alert
SELECT
Nodes.Uri
,Nodes.DisplayName
FROM Orion.Nodes AS Nodes

--Paste everything below this line into the Custom SWQL Alert
LEFT JOIN Orion.Routing.Neighbors AS Neighbors ON Neighbors.NodeID = Nodes.NodeID
RIGHT JOIN Orion.ContainerMembers cm on nodes.Uri = cm.Uri
WHERE 1=1
AND Neighbors.ProtocolName = 'Cisco EIGRP'
AND Neighbors.IsDeleted = 0
AND Neighbors.ProtocolStatusDescription = 'Established'
and cm.ContainerID = 22
GROUP BY Nodes.DisplayName, Nodes.NodeID
HAVING COUNT(*) IN (1, 3)
ORDER BY Nodes.DisplayName

Adam:  First let me say I really appreciate you trying to help me out!

Here's the current status.   The latest query you gave me:

SELECT Nodes.Uri,Nodes.DisplayName
FROM Orion.Nodes AS Nodes
LEFT JOIN Orion.Routing.Neighbors AS Neighbors ON Neighbors.NodeID = Nodes.NodeID
RIGHT JOIN Orion.ContainerMembers cm on nodes.Uri = cm.Uri
WHERE 1=1
AND Neighbors.ProtocolName = 'Cisco EIGRP'
AND Neighbors.IsDeleted = 0
AND Neighbors.ProtocolStatusDescription = 'Established'
and cm.ContainerID = 22
GROUP BY Nodes.DisplayName, Nodes.NodeID
HAVING COUNT(*) IN (1, 3)
ORDER BY Nodes.DisplayName

...  does not produce an error in SWQL Studio or in the alert custom swql trigger.   However, it does not produce any matches.  My original query as thus:

--------------------------------------------------------------------------------------------------------------------------
SELECT 
    cm.Name
FROM 
    (
        SELECT 
            c.Caption
        FROM 
            Orion.Routing.Neighbors AS n
        LEFT JOIN 
            Orion.NPM.Nodes AS d ON n.NodeID = d.ID
        LEFT JOIN 
            Cortex.Orion.Node AS c ON n.NodeID = c.NodeID
        WHERE 
            n.ProtocolName = 'Cisco EIGRP'
            AND n.IsDeleted = 0
            AND n.ProtocolStatusDescription = 'Established'
        GROUP BY 
            c.Caption
        HAVING 
            COUNT(*) IN (1, 3)
    ) AS nc
INNER JOIN 
    (
        SELECT 
            cm.Name
        FROM 
            Orion.ContainerMembers AS cm
        LEFT JOIN 
            Orion.Container AS c ON cm.ContainerID = c.ContainerID
        WHERE 
            cm.ContainerID = 22
    ) AS cm
ON 
    cm.Name = nc.Caption
ORDER BY 
    cm.Name

...produces a match (one line) on SWQL Studio.  So there's something fundamentally different about them but they're complex enough I'm not certain what the difference is.  Plus, I think that 1=1 argument isn't necessary, that was just me testing the logic before I put in the rest of the "ands" so it can be taken out.

Screenshots:

This is the point where not being in the same environment makes things hard unfortunately. It's not immediately clear to me why there's a difference and I dont have the data to figure it out.

I think you're near the finish line though

Me and chad.every  tried to start from the alert, those first few lines that are set in stone are ultimately what it's got to lead to in order to operate. We started from the Nodes table, as the nodes table is involved and it feels pretty "normal", but potentially you could alert on containers or containermembers or something instead, this is worth thinking about. You can either start here too and modify the query till it produces what you're looking for, or

You could go back to your original query, and get it working as-is in an alert, yours starts with SELECT cm.Name, we probably want to add an ID to that, and then we can join to that query on the ID. Note in yours you're selecting from the containermembers table (kinda)

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

RIGHT JOIN  

    (
        SELECT 
            c.Caption, n.NodeID, n.DisplayName
        FROM 
            Orion.Routing.Neighbors AS n
        LEFT JOIN 
            Orion.NPM.Nodes AS d ON n.NodeID = d.ID
        LEFT JOIN 
            Cortex.Orion.Node AS c ON n.NodeID = c.NodeID
        WHERE 
            n.ProtocolName = 'Cisco EIGRP'
            AND n.IsDeleted = 0
            AND n.ProtocolStatusDescription = 'Established'
        GROUP BY 
            c.Caption
        HAVING 
            COUNT(*) IN (1, 3)
    ) AS nc
    INNER JOIN 
    (
        SELECT 
            cm.Name, cm.MemberUri, cm.MemberPrimaryID, cm.MemberEntityType
        FROM 
            Orion.ContainerMembers AS cm
        LEFT JOIN 
            Orion.Container AS c ON cm.ContainerID = c.ContainerID
        WHERE 
            cm.ContainerID = 22
    ) AS cm
    ON 
    cm.Name = nc.Caption
    ORDER BY 
    cm.Name
    ) as original
 
On original.nodeid = nodes.nodeID

That doesnt work as intended, but I think you can see what i'm doing there in packaging up your query, and then using that to select specific sources from the alerting table, if you spent a bit of time with that you can probably also reach the goal. (current error is misliking the inner join, I think these could be done sequentially rather than call wrapped up, but that kinda goes against the example)

A final thing that's worth thinking about is what types of object exist in the container, and the relationships between them might not be what me and Chad are expecting, and if so maybe there's an incorrect join

This is the point where not being in the same environment makes things hard unfortunately. It's not immediately clear to me why there's a difference and I dont have the data to figure it out.

I think you're near the finish line though

Me and chad.every  tried to start from the alert, those first few lines that are set in stone are ultimately what it's got to lead to in order to operate. We started from the Nodes table, as the nodes table is involved and it feels pretty "normal", but potentially you could alert on containers or containermembers or something instead, this is worth thinking about. You can either start here too and modify the query till it produces what you're looking for, or

You could go back to your original query, and get it working as-is in an alert, yours starts with SELECT cm.Name, we probably want to add an ID to that, and then we can join to that query on the ID. Note in yours you're selecting from the containermembers table (kinda)

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

RIGHT JOIN  

    (
        SELECT 
            c.Caption, n.NodeID, n.DisplayName
        FROM 
            Orion.Routing.Neighbors AS n
        LEFT JOIN 
            Orion.NPM.Nodes AS d ON n.NodeID = d.ID
        LEFT JOIN 
            Cortex.Orion.Node AS c ON n.NodeID = c.NodeID
        WHERE 
            n.ProtocolName = 'Cisco EIGRP'
            AND n.IsDeleted = 0
            AND n.ProtocolStatusDescription = 'Established'
        GROUP BY 
            c.Caption
        HAVING 
            COUNT(*) IN (1, 3)
    ) AS nc
    INNER JOIN 
    (
        SELECT 
            cm.Name, cm.MemberUri, cm.MemberPrimaryID, cm.MemberEntityType
        FROM 
            Orion.ContainerMembers AS cm
        LEFT JOIN 
            Orion.Container AS c ON cm.ContainerID = c.ContainerID
        WHERE 
            cm.ContainerID = 22
    ) AS cm
    ON 
    cm.Name = nc.Caption
    ORDER BY 
    cm.Name
    ) as original
 
On original.nodeid = nodes.nodeID

That doesnt work as intended, but I think you can see what i'm doing there in packaging up your query, and then using that to select specific sources from the alerting table, if you spent a bit of time with that you can probably also reach the goal. (current error is misliking the inner join, I think these could be done sequentially rather than call wrapped up, but that kinda goes against the example)

A final thing that's worth thinking about is what types of object exist in the container, and the relationships between them might not be what me and Chad are expecting, and if so maybe there's an incorrect join