This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

port count

FormerMember
FormerMember

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:

    pastedImage_0.png

    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.

  • Sure, wluther‌, rub it it that we don't have UDT.   emoticons_laugh.png

    Yes, exactly like that.  Is there a way to do it without UDT, just through NCM or NPM?

  • 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

  • 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... emoticons_confused.png

    -Will

  • 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:

    Port count report.JPG

  • I like that idea, Tony.  Should that query work when pasted into SDK?  I tried it and came up with an error:

    Capture.JPG

  • 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.