Last 50 Route Changes Report

The intent of my request is to see at a glance of the most recent route changes.

Do you mean to say that you want a report that shows new subnets being added or removed to the enterprise? As in which subnet showed up that didn't exist before and where is it?

I don't think what you're asking exists in SolarWinds products right now, but I could be wrong. Maybe its in IPAM which I don't have. I found that in the report builder you can make a report to combine "Top 10 route flap" resources from multiple nodes. So if you know a set of core nodes that do routing you could see flaps for those. This will help you catch routing table changes but they are sorted by number of changes. The resource can go back in time pretty far (30 days I think), but I'm not sure it's what you're looking for.

It might be worth putting in a feature request if there isn't one for this already.

"Do you mean to say that you want a report that shows new subnets being added or removed to the enterprise?"

Yes.  I want at a glance look at the last 50 route changes across all nodes and be able to quickly see adds or removals during periods that are supposed to be quiet (e.g in a "change freeze" period.)

This will allow my engineers to quickly understand where an issue may be coming from.

Our core routers have a aggregated route entries.  So we would not be able to catch all route changes with only a handful of nodes.

This is something I whipped up based on my lab environment.  I'll admit my routing table skills are marginal so there may be some cleanup necessary depending on what you want to see but try it out and see if this gets you near where you want to be. 

From whatever view you want to use this just customize the view, add a custom query resource and paste this into the box.  You can set the number of lines to 50 or whatever other value you want.

SELECT nn.nodecaption, rt.Destination, rt.Mask, ni.InterfaceDescription, rt.NextHop, rt.RouteType, rt.RouteProtocol, rt.RouteAge, rt.NextHopAS, rt.Metric1, rt.Metric2, rt.Metric3, rt.Metric4, rt.Metric5, rt.LastDiscovery, rt.FirstDiscovery, rt.Missing

FROM NCM.RouteTable rt

join NCM.Nodes nn on nn.nodeid=rt.nodeid

left join NCM.Interfaces ni on rt.interfaceid=ni.interfaceid

order by routeage ASC

If you want to make it searchable then check the box to enable that and paste this version into the new text box that shows up

SELECT nn.nodecaption, rt.Destination, rt.Mask, ni.InterfaceDescription, rt.NextHop, rt.RouteType, rt.RouteProtocol, rt.RouteAge, rt.NextHopAS, rt.Metric1, rt.Metric2, rt.Metric3, rt.Metric4, rt.Metric5, rt.LastDiscovery, rt.FirstDiscovery, rt.Missing

FROM NCM.RouteTable rt

join NCM.Nodes nn on nn.nodeid=rt.nodeid

left join NCM.Interfaces ni on rt.interfaceid=ni.interfaceid

Where nn.nodecaption like '%${SEARCH_STRING}%' or  ni.InterfaceDescription  like '%${SEARCH_STRING}%' or  rt.Destination like '%${SEARCH_STRING}%'

order by routeage ASC

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

If I'm understanding this bit of the tables correctly the route age is determined at the time the network inventory job was last run, so in most environments that NCM job is scheduled to run once a day.  You could schedule that to run more often if you wanted better visibility into these changes, and you might end up deciding to set up some kind of where condition to exclude certain interfaces, protocols, or route types.

Hope that helps

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

mesverrum​, this is certainly interesting since I didn't think this was possible at all Thanks for sharing!

I added this to my server just to try. If I understand the query and the results correctly it takes all routing tables from all nodes and orders them by RouteAge. I'm not sure if this would work for t1n0m3n​, but it's a start for sure. Just needs a little more SQL filter magic *cough* cowbell

As I spend more time inside the database I'm finding more and more cases where I just replace a lot of the out of the box resources with my custom versions in SWQL or html or some other bits.  You can really make your Solarwinds interface amazingly more effective and automate away a lot of the annoying parts if you learn how to just pull what you want directly from the database instead of relying on built in resources and the GUI.  Of course without the GUI and built in resources as a sort of training wheels I would have never known where to start or what I was looking for.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems