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

Parents
  • 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'

Reply
  • 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'

Children
No Data