Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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

So I have a SWQL that shows me the routers we have (by name) that have only one OR only three EIGRP neighbors. This returns the node name of the router we need to go check. All the routers (in this group) that I'm checking should have either TWO or FOUR eigrp neighbors, never ONE or THREE.

The SWQL statement WORKS in SWQL Studio, but it does NOT allow me to add it as custom SWQL when crafting an alert:

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)
ORDER BY
c.Caption

Find more posts tagged with

Accepted answers

chad.every

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

All comments

Chris-S

Example from SWQL Studio:

Example from creation of alert:

Chris-S

And this same custom SWQL WORKS as a widget on our home screen for Orion:

chad.every

Working with SWQL Alerts requires some adjustment.

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)

--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

Chris-S

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.

adam.beedell

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