Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Cross-referencing routing neighbors to identify the name of the neighbor device

I'm trying to run a SQL query against SolarWinds to obtain a list of BGP neighbors to be used in another report, and need to include the name of the neighbor in the output. I'm trying to accomplish this by joining Orion.Nodes with Orion.Routing.Neighbors, using Orion.Nodes.IPAddress as a common property with Orion.Routing.Neighbors.NeighborIP, and outputting a column containing Orion.Nodes.Caption. The issue persists whether I use Caption, NodeName, or any other field I've tried from Orion.Nodes.

The trouble I'm running into is that some of these are returning just fine, but others are giving a NULL value for the caption. Meanwhile, for these same neighbor relationships, the GUI returns results just fine.

Example (with fake names & IP addresses, for obvious reasons) - R2 has a BGP neighbor relationship with R1, with neighbor IP of 10.0.8.3. I can see this plainly in the Routing Neighbors section of the SolarWinds GUI. But the query output shows "NULL" for the NeighborDevice (Orion.Nodes.Caption) on this row.

I'm not sure if this is an issue where I'm pulling the wrong fields/tables to get the results I need, if I'm matching against the wrong field (maybe the GUI has some pointer other than NeighborIP to figure out what the node on the other end is) or if my Orion.Nodes table is missing data that should be there (but if that's the case, how is the GUI resolving these?). I reached out to support, and was informed that the SDK is community-supported with no official support from SolarWinds, so here we are. Help me Thwack, you are my only hope!

Query being used is written as follows:

SELECT TOP 1000 NeighborID, NodeID, TOUPPER(R.Caption) as Device, TOUPPER(No.Caption) as NeighborDevice,NeighborIP, ProtocolID, ProtocolStatus, LastChange, ProtocolName, ProtocolStatusDescription, N.Status

FROM Orion.Routing.Neighbors N

JOIN Orion.Routing.Router R on R.NodeID = N.Nodeid

Left JOIN Orion.Nodes No on No.IPAddress = N.NeighborIP

Where N.ProtocolID = 14

order by N.NodeID

Find more posts tagged with

Accepted answers

All comments

christopher.t.jones123

@DJ.PBC, it's possible that the widget is also correlating from the Orion.NodeIPAddresses table, perhaps you do your join to hat table find the NodeID that it has listed for that node and then display the caption. The way you are doing it now requires that the IP matches the Polled IP of the device which may not always be the case. Something like below may work, I havent tested it fully

SELECT TOP 1000 NeighborID, NodeID, TOUPPER(R.Caption) as Device, TOUPPER(No.Caption) as NeighborDevice,NeighborIP, ProtocolID, ProtocolStatus, LastChange, ProtocolName, ProtocolStatusDescription, N.Status
FROM Orion.Routing.Neighbors N
JOIN Orion.Routing.Router R on R.NodeID = N.Nodeid
LEFT JOIN Orion.NodeIPAddresses IP on IP.IPAddress=N.NeighborIP
Left JOIN Orion.Nodes No on No.NodeID=IP.NodeID
Where N.ProtocolID = 14
order by N.NodeID

KMSigma.SWI

This custom query widget might meet your needs.

DJ.PBC

That returns different data, but it's still incomplete. I now have data where most of the Null values were in the original query, but I have Null values in a bunch of spots that had data before.

After some digging, I found that the Orion.Nodes.IPAddress used in my original query contains only the "Polling IP Address" for a given node, whereas Orion.NodeIPAddresses used in the new query is pulling the IP addresses of specific interfaces.

As a result, the old query will grab results for any node that has a polling IP but no interfaces set up (such as ISP devices we have BGP relationships with and have manually built dummy nodes purely for link-status monitoring). Meanwhile the new query grabs results for any Neighbor entries where the BGP neighbor relationship is with an interface other than the primary one used for polling. Sometimes these overlap, but often they do not.

Now the question becomes, is there any way to have a query run, and for any row where there is a NULL value in a column (in this case, the NeighborDevice column), have it run all of those through a 2nd query. Then give a single output with the results of both queries.