Custom Query for BGP Status

Question

I was curious if anyone has built a custom query resource for BGPs showing when the status is down or idle?  I'd like to set up a query that will pull the node name, neighbor ip and the neighbor status on a subset of nodes, filtering out specific neighbor IPs and showing the ones we care about.  Is this something that can be done in this resource with SWQL?

jvanderbeken · Answer

I made a report to check if on an node the bgp neighbors are idle or down. (with help of other thwack post) Some neighbors we are not interested in are filtert out We are using vrf's to do bgp peering so i had to add the vrf also select DISTINCT Nodes.Caption, NeighborIP, NPM_RoutingProtocol.DisplayName, NPM_RoutingProtocolStateMapping.DisplayName, NPM_VRFs.Name from dbo.NPM_RoutingNeighbor Inner join dbo.Nodes ON dbo.Nodes.NodeID = dbo.NPM_RoutingNeighbor.NodeID Inner join NPM_RoutingProtocol ON dbo.NPM_RoutingProtocol.ProtocolID = dbo.NPM_RoutingNeighbor.ProtocolID Inner join NPM_RoutingProtocolStateMapping ON dbo.NPM_RoutingProtocolStateMapping.ProtocolStatus = dbo.NPM_RoutingNeighbor.ProtocolStatus Inner join NPM_RoutingTable ON NPM_RoutingTable.NodeID = NPM_RoutingNeighbor.NodeID Inner join NPM_VRFs ON NPM_RoutingTable.VrfIndex = NPM_VRFs.VrfIndex where NeighborIP <> '172.30.241.2' and NeighborIP <> '172.30.241.7' and NeighborIP <> '172.30.241.10' and NPM_RoutingProtocolStateMapping.DisplayName not like 'Esta%' and NPM_RoutingProtocolStateMapping.DisplayName not like 'Excha%' and NPM_RoutingProtocolStateMapping.DisplayName not like 'Acti%' and NPM_RoutingTable.VrfIndex not like '' and NPM_VRFs.Name not like 'Mgmt-int%'

bharris1 · Answer

I was able to get a resource set up cobbling it together from some other Thwack posts.

How would I get this resource to display the status LED for each of these, If I include that column, it just shows the file name of the LED, such as RED.gif as an example. In addition, when I add protocol status description, it shows a number value instead of the string up, down, etc that shows in the report previewer. How would I get it to pull the string value of the number?

Here is the query I'm using without the status LED or status description:

SELECT TOP 1000 Nodes.Displayname AS [Node Name], NeighborIP AS [BGP Neighbor IP], AutonomousSystem AS [Autonomous System], ProtocolName as [Protocol Name], ProtocolStatusDescription AS [Protocol Status], LastChange AS [Last Change]

FROM Orion.Routing.Neighbors

Left outer JOIN orion.NodeIPAddresses

on NodeIPAddresses.IPAddressN=Neighbors.NeighborIPGUID

Left outer JOIN orion.Nodes

on nodes.NodeID= NodeIPAddresses.NodeID