Clear OPSF neighbor alerts only if acknowledged

We are having a problem when alerts are generated for ospf peers that have been decommissioned and will not come back. We are forced to rely on a defined value of, in this case 60 hours, time that has elapsed since the neighbor was changed. By doing this the decommissioned neighbors time-out after 60 hours, which is great.

However, the problem is when there is a legitimate issue that may take a while to resolve. Then the alert will clear and we will not be tracking this as a problem anymore.

What I'd like to do is have a way of only clearing an alert with the help of the Acknowledgement function.

I currently have the trigger condition setup as a Custom SWQL Alert:

SELECT Neighbors.Uri, Neighbors.DisplayName FROM Orion.Routing.Neighbors AS Neighbors

WHERE ( ( ( Neighbors.[ProtocolName] = 'BGP' ) AND ( Neighbors.[ProtocolOrionStatus] = '2' ) AND ( Neighbors.[IsDeleted] = '0' ) ) OR ( ( Neighbors.[ProtocolName] = 'OSPF' ) AND ( Neighbors.[IsDeleted] = '1' ) AND ( Neighbors.[LastChange] >= AddDate('Hour', -60,GetDate()) ) ) )

I'd like the OSPF neighbors clear only when they have been acknowledged as seen in this SQL query: SELECT TOP 1000 * FROM [dbo].[AlertActive] WHERE AlertActive.Acknowledged = 1

Hopefully this makes sense, I'm happy to clarify if needed. Any help is appreciated.

Find more posts tagged with

neighbor_change

ospf

routing_neighbors

Accepted answers

pvaysberg

After spending way too much time on this here is what I got. It seems to work. i_like_eggs, I appeciate the help in sending me in the right direction!

--SELECT *--FROM NPM_RoutingNeighbor_V  JOIN NPM_RoutingProtocol RP ON NPM_RoutingNeighbor_V.ProtocolID = RP.ProtocolID -- join on protocols for protocol status  JOIN Nodes N ON N.NodeID = NPM_RoutingNeighbor_V.nodeid -- check for unmanaged node  JOIN NPM_RoutingNeighbor RN ON RN.NeighborID = NPM_RoutingNeighbor_V.NeighborIDleft JOIN AlertStatusView asv ON ASV.ActiveObject = NPM_RoutingNeighbor_V.NeighborIDWHERE  --check if device is unmanaged N.UnManaged = 0  AND  (    -- CHECK BGP    (    RP.DisplayName = 'BGP'    AND NPM_RoutingNeighbor_V.ProtocolStatus != 6    AND NPM_RoutingNeighbor_V.IsDeleted != 1    )    -- CHECK OSPF      OR(      RP.DisplayName = 'OSPF'      AND NPM_RoutingNeighbor_V.ProtocolStatus != 8      AND        (        --Trigger alert if alert is not acknowledged or it has been less than xx time since LastChange        RN.LastChange >= dateadd(hh, -90, getutcdate())        OR  asv.Acknowledged = 0        )      )  )

All comments

pvaysberg

Bump.

i_like_eggs

Damn would have been good to answer this at work ill take a look for you tomorrow essentually you want to join the alertstatusview on this has a colloum acknowladged you want to set this as an additional check in your where clause to be acknowledge = 1. 0 being unacknowledged.

i_like_eggs

try this out it will check for Acknowledged but i couldn't get the Lastchange status in the OSPF for 60 hours this is because i am using SQL and not SWQL. (i just prefer SQL interface and flexibility) this is what the alert looks like:

Raw SQL:

SELECT *FROM NPM_RoutingNeighbor_V JOIN NPM_RoutingProtocol RP ON NPM_RoutingNeighbor_V.ProtocolID = RP.ProtocolID -- join on protocols for protocol status JOIN Nodes N ON N.NodeID = NPM_RoutingNeighbor_V.nodeid -- check for unmanaged node left JOIN AlertStatusView asv ON ASV.ActiveObject = NPM_RoutingNeighbor_V.NeighborIDWHERE --check if device is unmanaged and if the condition is ack'd, device can be in warning and still have BGP AND OSPF(n.UnManaged = 0 AND asv.Acknowledged = '0') AND-- CHECK BGP (((NPM_RoutingNeighbor_V.ProtocolID = 14) AND NPM_RoutingNeighbor_V.OrionStatus = 2) AND NPM_RoutingNeighbor_V.IsDeleted != 1) -- CHECK OSPF OR (((NPM_RoutingNeighbor_V.ProtocolID = 13) AND NPM_RoutingNeighbor_V.OrionStatus = 2) AND NPM_RoutingNeighbor_V.IsDeleted != 1)

i have tested the BGP but not the OSPF please feel free to make this swql and add the extra logic for the lastchange.

if anyone knows where i can find this 'last change' ill update in SQL

pvaysberg

Thanks for the response. I'm going to spend some time to get this going today and let you/everyone know how it went.

pvaysberg

Here is what I got. Needs some testing. I'll report back when confidence level is high

SELECT *

FROM NPM_RoutingNeighbor_V

JOIN NPM_RoutingProtocol RP ON NPM_RoutingNeighbor_V.ProtocolID = RP.ProtocolID -- join on protocols for protocol status

JOIN Nodes N ON N.NodeID = NPM_RoutingNeighbor_V.nodeid -- check for unmanaged node

JOIN NPM_RoutingNeighbor RN ON RN.NeighborID = NPM_RoutingNeighbor_V.NeighborID

left JOIN AlertStatusView asv ON ASV.ActiveObject = NPM_RoutingNeighbor_V.NeighborID

WHERE

--check if device is unmanaged and if the condition is ack'd, device can be in warning and still have BGP AND OSPF

n.UnManaged = 0

-- CHECK BGP

AND(

(

NPM_RoutingNeighbor_V.ProtocolID = 14

AND NPM_RoutingNeighbor_V.OrionStatus = 2

AND NPM_RoutingNeighbor_V.IsDeleted != 1

)

-- CHECK OSPF

OR(

NPM_RoutingNeighbor_V.ProtocolID = 13

AND NPM_RoutingNeighbor_V.OrionStatus = 2

AND NPM_RoutingNeighbor_V.IsDeleted != 1

AND (RN.LastChange >= dateadd(hh, -60, getdate()))

AND asv.Acknowledged = '0'

)

pvaysberg

After spending way too much time on this here is what I got. It seems to work. i_like_eggs, I appeciate the help in sending me in the right direction!

--SELECT *--FROM NPM_RoutingNeighbor_V  JOIN NPM_RoutingProtocol RP ON NPM_RoutingNeighbor_V.ProtocolID = RP.ProtocolID -- join on protocols for protocol status  JOIN Nodes N ON N.NodeID = NPM_RoutingNeighbor_V.nodeid -- check for unmanaged node  JOIN NPM_RoutingNeighbor RN ON RN.NeighborID = NPM_RoutingNeighbor_V.NeighborIDleft JOIN AlertStatusView asv ON ASV.ActiveObject = NPM_RoutingNeighbor_V.NeighborIDWHERE  --check if device is unmanaged N.UnManaged = 0  AND  (    -- CHECK BGP    (    RP.DisplayName = 'BGP'    AND NPM_RoutingNeighbor_V.ProtocolStatus != 6    AND NPM_RoutingNeighbor_V.IsDeleted != 1    )    -- CHECK OSPF      OR(      RP.DisplayName = 'OSPF'      AND NPM_RoutingNeighbor_V.ProtocolStatus != 8      AND        (        --Trigger alert if alert is not acknowledged or it has been less than xx time since LastChange        RN.LastChange >= dateadd(hh, -90, getutcdate())        OR  asv.Acknowledged = 0        )      )  )