Detect Flapping Route changes based on protocols

To create a query to alert on a flapping routing protocol.

First, for the Alert Engine, we need to Alert on nodes so we have to design a SQL query to return NodeId we can use the NPM_RoutingTable_Flap so that we also have the FlappingId to allow a count

We also want to be able to identify the Protocol we are interested in  so we need to pull data from the NPM_RoutingTable

So building the query will look like this we need to adjust the following

protocolid to identify the protocol we want to alert on  from NPM_RoutingProtocol

DATEADD(mi, how often the flap occurs in a set time

count(flapid) how many flaps need to occur before we alert.

One consideration is how ofter we poll for route change which is by default is 30 mins so a sensible check may be every 15mins

Once we have the query we can use the Web Console to create the alert

pastedImage_4.png

where nodeid in (SELECT distinct(nodeid) FROM [dbo].[NPM_RoutingTable_Flap]

where nodeid in (SELECT nodeid FROM [dbo].[NPM_RoutingTable]

where protocolid = in(Need to get protocl id from NPM_RoutingProtocol )

and [DateTime] > DATEADD(mi, -(Repalce with Number of Minutes) , GETDATE())

group by nodeid

having count(flapid) > Selct number of Flaps to trigger the alert )

Just to have the available protocols this is what is in the current NPM 12.5  NPM_RoutingProtocol

Protocols with Id's

 

protocoliddisplayname
0Unknown
0Unknown
1Other
1Other
2Local
2Local
3NetMgmt
3NetMgmt
4ICMP
4NDISC
5EGP
5RIP
6GGP
6OSPF
7Hello
7BGP
8RIP
8IDRP
9IsIs
9IGRP
10EsIs
11Cisco IGRP
12BBN SPF IGP
13OSPF
14BGP
15IDPR
16Cisco EIGRP
16Cisco EIGRP
17DVMRP
18RPL
19DHCP