1 of 1 people found this helpful
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.....
3 of 3 people found this helpful
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
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.
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).
4 of 4 people found this helpful
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)
From (Select InterfaceName From DiscoveredInterfaces Where (OperStatus = '1' and AdminStatus = '1')
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.
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
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...
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 !
1 of 1 people found this helpful
Ah, some of my Discovery Tables weren't cleaning out because there was a ton of stuff that didn't have an associated "ProfileID" in the Orion.DiscoveryProfiles table, without it there is no data associated to know when to clean things out! Cleaned them out and hopefully they'll stay that way!
Played a bit more and came up with something that might be useful for you. This is based on the DiscoveryTables, whereas my previous one was done off the NCM inventory. This query limits itself to the last discovery that was run and generates a list of all associated interfaces that are NOT in Orion at this time. The "caption" link takes you right into "Discover Interfaces" for that node so you can add them manually if you want.
I didn't ignore any interface types, which you might want to (like Loopbacks?), but that should be fairly easy to add.
One thing that threw me for a loop at first was the NodeID for >every< discovery starts over again, so for each profile you will have a node 1 for instance. Had to leverage the IP address to find the corresponding node in the NPM database, I would think that should work ok.
SELECT N.Caption, N.IPAddress, N.NodeID, DI.IfName, CONCAT('/Orion/Nodes/ListResources.aspx?Nodes=', N.NodeID) AS [_LinkFor_Caption]
FROM Orion.NPM.DiscoveredInterfaces DI
JOIN Orion.DiscoveredNodes DN ON ((DN.NodeID = DI.DiscoveredNodeID) AND (DN.ProfileID = DI.ProfileID))
JOIN Orion.Nodes N ON (DN.IPAddress = N.IPAddress)
LEFT OUTER JOIN Orion.NPM.Interfaces I ON ((N.NodeID = I.NodeID) AND (DI.InterfaceIndex = I.InterfaceIndex))
WHERE (DI.OperStatus = 1) AND (DI.AdminStatus = 1) AND (I.NodeID IS NULL)
AND (DI.ProfileID = (SELECT TOP 1 DP.ProfileID AS MyProfileID FROM Orion.DiscoveryProfiles DP ORDER BY ProfileID DESC))
ORDER BY NodeID
Seems to work well from the limited testing I've done, HTH!!