-
Re: port count
cjfranca Nov 12, 2015 8:26 AM (in response to fnfn)to configure reports the last month ant show this reports in a view.
-
Re: port count
rschroederNov 12, 2015 9:16 AM (in response to fnfn)
That's a good question. I looked over the Reports with "Interface" in their title, came up with one that sounded like a winner--just simply the "Interfaces" Report. But when I run it I receive an error due to the size of my network:
I can't set it to 68538 rows--it just won't let me.
That tells me NCM thinks I have 68538 Interfaces. But that will include all routing interfaces--anything logical in addition to everything physical. In this case that's not what I want to know--I want to know the actual number of ports I own.
Well, I could say I've got 800 switches and multiply by 48, but a lot of those switches are 4510's with varying number of blades in them. From three to eight line cards means from 146 ports to 392 ports per 4510; suddenly my actual number of ports becomes very vague, and looks more like an estimate or a guess.
So, since I'm looking for ports instead of interfaces, I found an NCM Inventory report called "Switch Ports." Sounds perfect! But no, it only gave me 232 lines, some of which are duplicates.
So I'm stuck, like you. What's the tool or report that tells me the accurate number of physical switch & router ports on my network?
I'm rerunning the Inventory, hoping that's all it takes to get the Switch Ports report to become accurate. In the meantime I'll be watching this thread to see what others are recommending.
-
Re: port count
wlutherNov 12, 2015 9:40 AM (in response to rschroeder)
rschroeder If a solution does not present itself in the NPM/NCM setup, and you have the time, perhaps load up a VM and install the UDT module. Then snoop around in there and see if you find something that gives you an accurate count. I would think the UDT module would surely be capable of this, though, I would also expect this basic functionality within NPM.
-
-
Re: port count
rschroederNov 12, 2015 9:46 AM (in response to wluther)
Sure, wluther, rub it it that we don't have UDT.
Yes, exactly like that. Is there a way to do it without UDT, just through NCM or NPM?
-
Re: port count
wlutherNov 12, 2015 9:58 AM (in response to rschroeder)
rschroeder No worries, we are in the same boat here... I would love to have the UDT module, however, it is simply at a price point that does not match our use cases yet... Hopefully, some day soon, we can get this one.
As far as doing this in NCM, perhaps you can take a look at the "Cirrus.EntityPhysical" table, through the SDK, and then filter it by the "EntityClass" column.
I THINK, and I stress THINK, EntityClass=10 will show you only the physical ports, from that table.
If I had it to guess, I would say there is probably a better place for this, somewhere. (perhaps the Cirrus.Interfaces table) However, I just took a quick look, and that table looked like a good start.
Let me know.
Thank you,
-Will
-
Re: port count
cahuntJan 25, 2016 9:32 AM (in response to wluther)
I was thinking you would need to monitor each interface, but polling the interface details and interface status tables should give you node specific details you can display in a table for each node view.
-
-
Re: port count
wlutherNov 12, 2015 10:55 AM (in response to rschroeder)
rschroeder After you get the SDK installed and running, try the SWQL query below to view all of the different interface types you have, and how many of each you are working with.
SELECT DISTINCT(Type), TypeName, TypeDescription, InterfaceType, COUNT(InterfaceType) AS tncount, InterfaceSubType, InterfaceTypeName, InterfaceTypeDescription, Description FROM Orion.NPM.Interfaces GROUP BY TypeDescription, InterfaceType, InterfaceSubType, InterfaceTypeName, InterfaceTypeDescription, Description
This should give you a simple count of how many of each different interface type you have. (as usual, with anything from me, there is most likely a better way to do it...)
You can use this to determine which types of interfaces you want to include/exclude. ("Type" or "InterfaceType)
So, if you look through all of the different interface types, and decide you want to count InterfaceTypes 37, 49, & 94, then you can put those types into the SWQL query below, and it should add them all up and give you the total.
SELECT COUNT(*) AS totcount FROM Orion.NPM.Interfaces where InterfaceType IN ('37','49','94')
Again, nothing fancy here, and this very well may not be the best, or even most accurate, way to do it, but hey, I do not charge anything for my incorrect knowledge...
-Will
-
-
-
-
-
Re: port count
Tony VispettoNov 12, 2015 1:51 PM (in response to fnfn)
1 of 1 people found this helpfulNPM out of the box can't provide what you are looking for. Since we do not have UDT, I had to get creative and leverage data from NCM and NPM.
Here is a SQL report I use to determine the total number of interfaces on a device, # of used interfaces, # of available interfaces, and percent available. You can run schedule the report to run on a monthly basis and then check the deltas. This report does not take into account any traffic over an interface. It just checks for the Operational Status of the port at the time the report is ran. So if any end point devices are shut-down at the time the report is run that interface will be added as an available port.
SELECT NodeID, Caption,
COUNT (*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Available Ports",
CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
FROM (
SELECT ND.Caption, ND.NodeID,
CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
WHERE (InterfaceTypeName like '%ethernet%')
) T1
GROUP BY NodeID, Caption
ORDER BY Caption DESC
I created a web based SQL report and the output is shown below:
-
-
Re: port count
wlutherNov 12, 2015 2:15 PM (in response to rschroeder)
No, that is not SWQL, so you will not be able to use it directly in the SDK.
You need to use the database manager app, SQL Server Management Studio (SSMS), or some other SQL tool to run that query.
-
Re: port count
rschroederNov 12, 2015 3:36 PM (in response to wluther)
Ah, no wonder it fails.
Thanks.
Rick S
-
-
Re: port count
Tony VispettoNov 12, 2015 2:16 PM (in response to rschroeder)
1 of 1 people found this helpfulThis is a SQL report not SWQL and will not work in the SDK.
Create a new web based report > select custom table > Selection method Advanced DataBase Query > Select SQL radio button > paste query there
-
-
Re: port count
cahuntNov 12, 2015 2:43 PM (in response to Tony Vispetto)
is this reliant on you having all interfaces monitored?
-
Re: port count
wlutherNov 12, 2015 2:57 PM (in response to cahunt)
cahunt Yes, I believe this only produces results based on interfaces that are already being monitored. I think UDT is the only way to get results without monitoring everything... but not sure.
You might be able to trick it by setting up a scheduled polling job, then alter the query to point to the results of that job, and join it with interfaces table... hmmm... I might need to check that out and test it...
-Will
-
Re: port count
Tony VispettoNov 12, 2015 3:04 PM (in response to cahunt)
It's not dependent on monitored interfaces, NPM functions that way but not NCM. Once a node becomes a NCM managed node all of the interfaces on the device are added to the NCM_Interfaces table.
-
Re: port count
wlutherNov 12, 2015 3:10 PM (in response to Tony Vispetto)
Ah, very cool. That is a good thing to know.
So, just to clarify this, for myself, if I add a switch to NPM, and only choose to monitor 5 out of 10 interfaces, then the database is only going to ever show 5 interfaces for that switch. Now, I add that switch to NCM, still only monitoring those 5 interfaces, and the database will now show all 10 interfaces, even when I have only added the 5?
Thank you very much for the info.
-Will
-
-
-
Re: port count
rschroederNov 12, 2015 4:05 PM (in response to Tony Vispetto)
I built this in Report Writer exactly as described; it worked perfectly. Now I have a complete count of total ports, used ports, available ports, and percentage of available ports on every device.
Tony, you are the wind beneath my wings!
Now, to figure out how to modify the Report to not only sort on the Caption, but so devices show up alphabetically . . .
-
Re: port count
Tony VispettoNov 12, 2015 4:25 PM (in response to rschroeder)
Happy to help. I spent a lot of time trying to figure this one out.
Change the following:
ORDER BY Caption DESC
to ORDER BY Caption ASC
and it will show up alphabetically.
-
-
-
Re: port count
Tony VispettoNov 12, 2015 5:58 PM (in response to wluther)
1 of 1 people found this helpfulYou can test how NPM and NCM collect interface inventory by running the following queries:
To see how many interfaces are monitored by NPM for a device, change the X's to reflect the device name:
SELECT
NodesData.Caption,
Interfaces.InterfaceName
FROM NodesData
INNER JOIN Interfaces Interfaces ON Interfaces.NodeID = NodesData.NodeID
WHERE NodesData.Caption = 'XXXXX'
To see how many total interfaces are in the NCM_Interface table for a device:
Again change the X's to reflect the device name:
SELECT Caption, InterfaceName
FROM (
SELECT ND.Caption, NCMI.InterfaceName
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
WHERE (Caption = 'XXXXX')
) T1
GROUP BY Caption, InterfaceName
ORDER BY Caption ASC
The output above will show all interfaces including loopbacks, Vlans, etc.
That is why I have the ethernet filter applied in the query below to only show the physical interfaces (removing loopbacks, Vlans, etc.)
SELECT NodeID, Caption,
COUNT (*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Available Ports",
CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
FROM (
SELECT ND.Caption, ND.NodeID,
CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
WHERE (InterfaceTypeName like '%ethernet%')
) T1
GROUP BY NodeID, Caption
ORDER BY Caption ASC
-
Re: port count
fnfn Nov 13, 2015 5:25 AM (in response to Tony Vispetto)thanks Tony, how can I change following querie only use NPM?
SELECT NodeID, Caption,
COUNT (*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Available Ports",
CAST (ROUND ((1.0 * SUM (IsDown)/COUNT (*)) *100, 0) as float) AS "% Available"
FROM (
SELECT ND.Caption, ND.NodeID,
CASE WHEN OperStatus = 'Up' THEN 1 ELSE 0 END AS IsUp,
CASE WHEN OperStatus = 'Down' THEN 1 ELSE 0 END AS IsDown
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
WHERE (InterfaceTypeName like '%ethernet%')
) T1
GROUP BY NodeID, Caption
ORDER BY Caption ASC
-
Re: port count
Tony VispettoNov 13, 2015 9:33 AM (in response to fnfn)
NPM out of the box can't provide what you are looking for. I had to get creative and leverage data from NCM and NPM.
-
-
-
-
-
-
Re: port count
sja Feb 20, 2016 8:01 AM (in response to Tony Vispetto)Hi Tony
I upgrate NCM to 7.4 probably because of that thread...
so thanks one more for sharing that NPM/NCM report.
Did you try to join customs property table into that report?
Could be very nice if it will work...
Try to get it to work...
POP-NODE-INTERFACE-STATUS in my report
SELECT POP,Caption,InterfaceDescription,InterfaceAlias
FROM (
SELECT ND.Caption, NCMI.InterfaceAlias,NCMI.OperStatus,NCMI.InterfaceDescription,NodesCustomProperties.POP
FROM NCM_Interfaces NCMI INNER JOIN NCM_NodeProperties NCMNP ON (NCMI.NodeID = NCMNP.NodeID)
INNER JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)
INNER JOIN NodesCustomProperties ON (NodesCustomProperties.POP = POP)
WHERE (Caption like '%TargetSwitch' AND InterfaceAlias like '%HP%' AND OperStatus='Down')
) T1
GROUP BY POP,Caption, InterfaceDescription,InterfaceAlias
ORDER BY Caption ASC
-
-
Re: port count
wlutherNov 12, 2015 2:26 PM (in response to fnfn)
-
-
Re: port count
CourtesyITFeb 20, 2016 2:35 PM (in response to cjfranca)
So is this what we can expect from this report?
-