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