Again, this week a request for Solarwinds reporting utilizing some SQL knowledge on my part. The idea was to create a report on DMVPN connections. The build-in web reporter does a fairly decent job creating this report already. However, the IP addresses in this report do not reflect the actual interface addresses (or perhaps it’s an user issue ;) ).
Query Solarwinds
Start by opening and connecting to the Solarwinds database in SWQL Studio. This allows you to check the results and finetune the below query. So, to solve this, I set out to use the SWQL Studio again to find my tables and build a working query. I quickly found a couple of tables such as:
- Orion.NodeIPAddresses
- Orion.Nodes
- Orion.NPM.Interfaces
- Orion.NPM.InterfacesCustomProperties
Next, I retrieve the node’s IP address and some node specific details such as Caption with a Left Join as I want to keep all data from NodeIPAddresses, even if there is no caption:
SELECT Nodes.Caption, NodeIP.IPAddress, Nodes.Location, Nodes.MachineType, Nodes.IOSVersion
FROM Orion.NodeIPAddresses AS NodeIP
LEFT JOIN Orion.Nodes AS Nodes ON NodeIP.NodeID = Nodes.NodeID
Then, you’re able to join the Interfaces on the unique NodeID from our NodeIPAddresses table, as well as the InterfaceIndex, allowing to get data such as Bandwidth. This can later be calculated to Gbit/s, Mbit/s etc. in the reporting table.
SELECT Nodes.Caption, NodeIP.IPAddress, Interfaces.Name, Interfaces.Speed AS Mbit, Nodes.Location, Nodes.MachineType, Nodes.IOSVersion
FROM Orion.NodeIPAddresses AS NodeIP
LEFT JOIN Orion.Nodes AS Nodes ON NodeIP.NodeID = Nodes.NodeID
LEFT JOIN Orion.NPM.Interfaces AS Interfaces ON NodeIP.NodeID = Interfaces.NodeID AND NodeIP.InterfaceIndex = Interfaces.Index
Joining the InterfaceCustomProperties as defined in our Solarwinds instance. Also, some nodes are outside of the monitoring scope for this region, so these are filtered with a WHERE statement:
SELECT Nodes.Caption, NodeIP.IPAddress, Interfaces.Name, Interfaces.Speed AS Mbit, Nodes.Location, Nodes.MachineType, Nodes.IOSVersion
FROM Orion.NodeIPAddresses AS NodeIP
LEFT JOIN Orion.Nodes AS Nodes ON NodeIP.NodeID = Nodes.NodeID
LEFT JOIN Orion.NPM.Interfaces AS Interfaces ON NodeIP.NodeID = Interfaces.NodeID AND NodeIP.InterfaceIndex = Interfaces.Index
INNER JOIN Orion.NPM.InterfacesCustomProperties AS InterfacesCustom ON Interfaces.InterfaceID = InterfacesCustom.InterfaceID
WHERE Nodes.Caption NOT LIKE 'p%'
Because in this environment, custom properties have been added to the WAN connection to identify the interfaces for reporting. This is something that has to be done in advance to allow more specific reporting. The data can be found in the Orion.NPM.InterfacesCustomProperties table:
SELECT Nodes.Caption, NodeIP.IPAddress, InterfacesCustom.WAN_Interface_Type, Interfaces.Name, Interfaces.Speed AS Mbit, Nodes.Location, Nodes.MachineType, Nodes.IOSVersion
FROM Orion.NodeIPAddresses AS NodeIP
LEFT JOIN Orion.Nodes AS Nodes ON NodeIP.NodeID = Nodes.NodeID
LEFT JOIN Orion.NPM.Interfaces AS Interfaces ON NodeIP.NodeID = Interfaces.NodeID AND NodeIP.InterfaceIndex = Interfaces.Index
INNER JOIN Orion.NPM.InterfacesCustomProperties AS InterfacesCustom ON Interfaces.InterfaceID = InterfacesCustom.InterfaceID
WHERE Nodes.Caption NOT LIKE 'p%'
AND (InterfacesCustom.WAN_Interface_Type = 'WAN-Local ISP Interface' OR InterfacesCustom.WAN_Interface_Type = 'WAN-DMVPN Tunnel Interface')
If you don’t have these custom properties, try to find something else that distinguished the connection types you’re trying to filter on. For example, if you use different IP ranges on tunnels, this could definitely be helpful in filtering as well.
Creating a report
In Solarwinds, go to Reports > All Reports> Manage Reports (top right) > Create New Report. In this screen, choose a custom table.
Then, choose Advanced Database Query and select SWQL.
After this, you’re redirected to a screen to create the report. Click Add Column and select all fields and click Add Column. It’s possible to rearrange the fields accordingly. Under Mbit, click Advanced and set the Display setting to Data Unit and leave the rest default. On the bottom, make sure to group and sort as this creates a nicer lay-out of the report.
After this, the other settings are up to you and not very exciting.
Adding subnet data
Subnet data was interesting to have as well for the DMVPN sites. The best way was to recover this data was starting over with the Orion.Routing.RoutingTable as basis. The above query was rewritten to fit with the new basetable:
SELECT DISTINCT Nodes.Caption, NodeIP.IPAddress, InterfacesCustom.WAN_Interface_Type, Interfaces.Name, Interfaces.Speed AS Mbit, Nodes.Location, Routing.RouteDestination, Routing.RouteMaskLen, Nodes.MachineType, Nodes.IOSVersion
FROM Orion.Routing.RoutingTable AS Routing
FULL JOIN Orion.NodeIPAddresses AS NodeIP ON Routing.RouteNextHop = NodeIP.IPAddress
LEFT JOIN Orion.Nodes AS Nodes ON NodeIP.NodeID = Nodes.NodeID
LEFT JOIN Orion.NPM.Interfaces AS Interfaces ON NodeIP.NodeID = Interfaces.NodeID AND NodeIP.InterfaceIndex = Interfaces.Index
INNER JOIN Orion.NPM.InterfacesCustomProperties AS InterfacesCustom ON Interfaces.InterfaceID = InterfacesCustom.InterfaceID
WHERE Nodes.Caption NOT LIKE 'p%'
AND (InterfacesCustom.WAN_Interface_Type = 'WAN-Local ISP Interface' OR InterfacesCustom.WAN_Interface_Type = 'WAN-DMVPN Tunnel Interface')
This query can generate more rows than the earlier query. This would be the result of several routes being advertised per site. The report table has to be reconstructed after changing the query.
Tips
If you want to filter on routing protocols, check the result of this query to find which how Solarwinds has mapped the routing protocols:
SELECT ProtocolID, DisplayName, IP_Version
FROM Orion.Routing.RoutingProtocol
To find the connected tunnels on a DMVPN headend:
SELECT RouteDestination, RouteNextHop, RouteMaskLen, LastChange, InterfaceName, ProtocolID, Metric
FROM Orion.Routing.RoutingTable
WHERE NodeID = '1964'
AND ProtocolID = '16'
AND InterfaceName LIKE 'Tunnel%'
Where NodeID can found by checking the Orion.Nodes table and the ProtocolID is in this case EIGRP.