Need to adjust the alerting on section when setting up swql alert

Question

HI All

I am trying to setup an alert for when our firewalls loose connection to both Active/standby panoramas

I have got it working in swql with:

SELECT Nodes.Uri, Nodes.DisplayName, P.CurrentValue as [Primary Pano], S.CurrentValue as [Secondary Pano] FROM Orion.Nodes AS Nodes

left Join (
Select NodeID, CurrentValue from Orion.NPM.CustomPollerAssignment
WHERE (CustomPollerOid = '1.3.6.1.4.1.25461.2.1.2.4.1')
) P on P.NodeID = Nodes.NodeID

left Join (
Select NodeID, CurrentValue from Orion.NPM.CustomPollerAssignment
WHERE (CustomPollerOid = '1.3.6.1.4.1.25461.2.1.2.4.2')
) S on S.NodeID = Nodes.NodeID

WHERE P.CurrentValue != 'Connected' AND S.CurrentValue != 'Connected'

But when creating the custom swql query i am unable to adjust the  Select section:

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

Could anybody assist

Thanks

fraser-sky · Answer

Thanks to you both, I have the alert working

Not sure if there is a more SQL efficient way to run query but it works

this is the adjusted query

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

left Join (
Select NodeID, CurrentValue from Orion.NPM.CustomPollerAssignment as P
WHERE (CustomPollerOid = '1.3.6.1.4.1.25461.2.1.2.4.1')
) P on P.NodeID = Nodes.NodeID

left Join (
Select NodeID, CurrentValue from Orion.NPM.CustomPollerAssignment as S
WHERE (CustomPollerOid = '1.3.6.1.4.1.25461.2.1.2.4.2')
) S on S.NodeID = Nodes.NodeID

WHERE  P.CurrentValue != 'Connected' AND S.CurrentValue != 'Connected'

bobmarley · Answer

Here is another way to think about it as you construct your query.

The default "SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes"  is pretty much the equivalent of using the select below.

I believe all of these fields are available to use with the remainder of your query.

Below is an example using the default "SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes"

If you uncomment any of the Where conditions below you will be returned a result.

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
--Where Caption LIKE '%nodename%' --Assuming you have a node with this name
--Where IPAddress LIKE '10.168.44.12' --Assuming you have a node with this IP
--Where Community LIKE 'public' ----Assuming you have a node with public as a community string
--Where NodeID LIKE '28424' --Assuming you have a node with this NodeIDHope this helps! Looks like you just need to re-word your query a bit.

yaquaholic · Answer

On the alert actions, you can add SWQL code to the message/subject that will allow you to pull the primary/secondary information you need into the subject/message body.

Have a look at @BobMarley';s post - thwack.solarwinds.com/.../how-to-create-and-use-your-own-define-sql-swql-variable-advanced-within-an-alert