Hi,
How can I create interface usage table for network devices? This table should display number of total, available, using ports for last month.
regards,
to configure reports the last month ant show this reports in a view.
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.
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.
fnfn & rschroeder
Like this, yes?
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?
NPM 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:
I like that idea, Tony. Should that query work when pasted into SDK? I tried it and came up with an error:
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.
This 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
Ah, no wonder it fails.
Thanks.
Rick S
fnfn Does vispetto's response answer your question, and provide you with the results you are looking for?
Re: port count
If so, please mark his response as the correct answer. This will assist others with the same/similar question.
Thank you,
-Will
is this reliant on you having all interfaces monitored?
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...
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.
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.
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
SELECT ND.Caption, NCMI.InterfaceAlias,NCMI.OperStatus,NCMI.InterfaceDescription,NodesCustomProperties.POP
INNER JOIN NodesCustomProperties ON (NodesCustomProperties.POP = POP)
WHERE (Caption like '%TargetSwitch' AND InterfaceAlias like '%HP%' AND OperStatus='Down')
GROUP BY POP,Caption, InterfaceDescription,InterfaceAlias
ORDER BY Caption ASC
Look this report
So is this what we can expect from this report?
