Database query to make sure routing is being monitored

temark

Last night we lost BGP to the carrier on one of our primary internet routers, and no one got alerted.  We found that someone, for unknown reason, had unchecked Routing, (including Routing Table, BGP, and OSPF).

Now Management wants verification that it is not un-checked on any other routers. Rather than manually check all 275 routers we're monitoring, I'd like to do a database query to see the status of those fields for our routers, (labeled WAN Router in the Device Role field), and if need be, fix them.

I'm not much of a database guy, so I'm throwing this one out to the community.

blsanner

So, I see two ways you could do this:

First, instead of a database query, you could go to Settings --> Manage Pollers, then select the Routing poller and click on the "Assign" button.

This will bring up a list of your nodes and show you which ones are a match and which ones are enabled.  You can also group by your custom properties here to make it easy to see where it is not enabled and you could enable it right from this screen.

The second option would be a SQL query which would look something like this:

SELECT Caption, IP_Address

FROM Nodes

WHERE DeviceRole = 'WAN Router'

AND NodeID NOT IN (

        SELECT NodeID FROM NPM_Routing)

You may need to adjust the DeviceRole custom property name as "Device Role" as you listed it above is not correct as a custom property cannot have a space in the name.  Also, this will only show you where it is not assigned.  You would still need to go back to either individual nodes or the Manage Pollers screen to enable it.