How to Discover & Report on Resources NOT PRESENT in SolarWinds Already?

This should give you what you're looking for Alex.

Run a discovery, don't import anything but make sure it completes
Query the 'DiscoveryProfiles' and identify your latest 'ProfileID'
Run this query, replace the 'REPLACE ME' value (Kind of messy and could use optimizing, but wrote this on the fly)

Select DiscoveredNodes.SysName,L.InterfaceName

From (Select InterfaceName From DiscoveredInterfaces Where (OperStatus = '1' and AdminStatus = '1')

except

Select InterfaceName From Interfaces ) AS L

INNER JOIN DiscoveredInterfaces ON L.InterfaceName = DiscoveredInterfaces.InterfaceName

INNER JOIN DiscoveredNodes ON DiscoveredInterfaces.ProfileID = DiscoveredNodes.ProfileID

Where DiscoveredInterfaces.ProfileID > 'REPLACE ME'

This will give you a report of interfaces with the node name of interfaces not currently in Orion. You could automate this process by introducing dynamic date values and using the report scheduler, that way you don't have to lift a finger for future reports.

Thanks,

David

All comments

Hey Alex,

The way that I accomplish this task is by descovering target devices, subnets, or networks. If you run a discovery and import all interfaces Orion will filter out dupplicates and only import new interfaces and in turn deliver the result you seek. Depending on your network size you might have to segment the scans.

If you really want a report that will group missing resources by node I can write you up a query.....

Thanks,

David

Agreed, if you're looking for interfaces use discovery, works great. You should be able to customize a scan to select the type of interface you want (ie: up/trunk, up/access, etc).

For devices I tend to use CDP Neighbor for that and have written an SWQL query that produces a table of nodes that I see via CDP neighbor but aren't in Orion. Not perfect, still in dev stages (and might always be!), but it works well enough for me now. Need to rework it to use LLDP also, or make another report with it. But it does come up with devices I don't already know about.

SELECT CDP.RemoteDevice, CDP.RemoteIPAddress, CDP.RemotePlatform, CDP.Node.SysName AS ConnectedTo, CDP.Node.AgentIP AS Connected2IP,

(SELECT IfName from Orion.NPM.Interfaces NI WHERE (NI.NodeID = CDP.Node.CoreNodeID) AND (CDP.IfIndex = NI.Index)) AS [LocalInt]

FROM NCM.CiscoCdp CDP left outer join Orion.NodeIPAddresses N ON (CDP.RemoteIPAddress = N.IPAddress)

WHERE (N.IPAddress IS NULL) AND NOT (CDP.RemotePlatform LIKE '%AIR-%') AND NOT (CDP.RemotePlatform LIKE 'Cisco AP%') AND NOT (CDP.RemoteCapability = '00000090')

ORDER BY RemoteCapability, Connected2IP, RemoteIPAddress, RemotePlatform DESC

Very interesting method Craig, thanks for sharing (Will be reading more of your posts). I think this should give you a basic overview of missing devices in Orion as well using the CDP/ NodeIP table.

Select IpAddress From NodeCiscoCdpEntries

except

Select IpAddress From NodeIPAddresses

That's very very SQL - thanks a lot

Hmmm... that's the trick - I do want to report on them BEFORE I will import them into Orion. When I run discovery - it basically picks up everything, including stuff that I already have. What I need is to somehow differentiate non-existing from existing and produce a report. This is report would normally be used "for revision by network team". If they will find anything that needs to be monitored - then yes, we will go ahead and add missing stuff.

Any thoughts?

Hmm... So what you're saying is that, in addition to the CDP stuff maybe, you want a report on what interfaces on which devices are UP, but not in Orion?

Not saying its not possible, but if I'm not wrong NPM only looks at what interfaces are monitored, so I don't think it could be used. Might be able to get the info from the NCM Inventory-side, but Inventory isn't usually updated all that often. UDT might be another route, but I think its possible to run into the same dead-end as NPM, not sure.

What modules do you have on your Orion installation?

It is standard set - SAM, NPM, NCM...

Exactly, I need to somehow to know which interfaces are UP but not in SW. I would have though that Discovery would be perfect place for this functionality - run the job > see what's missing > add what's needed > done! ... it looks like however this is not possible and I was just wondering how you guys keep up with constant changes by your network team(s).

This should give you what you're looking for Alex.

Run a discovery, don't import anything but make sure it completes
Query the 'DiscoveryProfiles' and identify your latest 'ProfileID'
Run this query, replace the 'REPLACE ME' value (Kind of messy and could use optimizing, but wrote this on the fly)

Select DiscoveredNodes.SysName,L.InterfaceName

From (Select InterfaceName From DiscoveredInterfaces Where (OperStatus = '1' and AdminStatus = '1')

except

Select InterfaceName From Interfaces ) AS L

INNER JOIN DiscoveredInterfaces ON L.InterfaceName = DiscoveredInterfaces.InterfaceName

INNER JOIN DiscoveredNodes ON DiscoveredInterfaces.ProfileID = DiscoveredNodes.ProfileID

Where DiscoveredInterfaces.ProfileID > 'REPLACE ME'

Thanks,

David

Well, we tend to not monitor all interfaces on all switches, only uplinks. With the exception being in our Datacenter, which we run a discovery on every so often and see whats new. We also don't tend to need a review.

Remember that you can run a discovery without doing the import at that point in time. You can then go to the discovery results and see what is potentially new there and import from that.

Not 100% sure what tables the discovery info is kept in, have yet to find them. Did work this up though:

SELECT TOP 1000 I.InterfaceIndex, I.InterfaceAlias, I.InterfaceName, I.Node.CoreNodeID, I.InterfaceDescription, CONCAT('/Orion/Nodes/ListResources.aspx?Nodes=', I.Node.CoreNodeID) AS [_LinkFor_CoreNodeID]

FROM NCM.Interfaces I LEFT OUTER JOIN Orion.NPM.Interfaces I2 ON (I.Node.CoreNodeID = I2.NodeID) AND (I.InterfaceIndex = I2.InterfaceIndex)

WHERE (I.OperStatus = 'UP') AND (I.AdminStatus = 'UP') AND (I2.NodeID IS NULL) AND NOT (I.InterfaceName LIKE 'Ba%') AND (I.InterfaceDescription LIKE '%Ethernet%')

ORDER BY CoreNodeID

It for the most part compares what NCM has to what NPM has and reports the differences for Interfaces that are "UP/UP". I have it ignoring all the "Backplane-GigabitEthernet%" (ie: 'Ba%') interfaces since I had a ton of those in my database. You will probably want to customize your filtering...

Oh cool, looks like someone else came up with a solution using discovery while I was playing with this. I'll go ahead and post this anyway as an alternative...

This might come in handy. (discovery tables)

Discovery Information.JPG

Yea, I found the "DiscoveredInterfaces" table, but it had LOTS of nodes in it that no longer existed so I decided not to play with it anymore. I think the use of the "ProfileID" would solve that problem, but I thought I'd play with using NCM inventory instead.

I'll have to see if there is a way to keep that table "clean" so I wouldn't have to play with the ProfileID to get what I want. Probably be good to get rid of stale discoveries anyways I'm thinking.

Either way, using NCM Inventory is another approach, always nice to show different ways to a potential solution! How "fresh" my solution is would depend on how often you have an inventory run, but it has the added benefit of not having old nodes in it by default...

You can define how long those tables stay populated via the 'Discovery Retention' in the Orion Polling Settings.

It's been a while since I played with the NCM tables, your approach gives me more ideas... so thanks !

David, you are Magician! Thank You so much. I can already see this working like a charm... Rolling sleeves - diving into SQL I will post back here my final solution in a few days I hope