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

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

  • For the alert trigger you won't need to return the Primary or Secondary Pano connected values, just to know that they are not connected will be enough. 
    You can then pull the Primary or Secondary Pano connected values into the alert subject or message, using the "Define SQL/SWQL Variable (Advanced)" option.

  • Hi yaquaholic

    Thanks for the reply

    I should have mentioned I am a network engineer and sql is not my thing , it took me forever to get alert working in swql.

    So not sure what you mean by pull values into alert.

  • 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

  • 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 NodeID
    Hope this helps! Looks like you just need to re-word your query a bit. 

  • 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 Slight smile

    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'