28 Replies Latest reply on Feb 20, 2016 2:35 PM by CourtesyIT

    port count

    fnfn

      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,

        • Re: port count
          cjfranca

          to configure reports the last month ant show this reports in a view.

          • Re: port count
            rschroeder

            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
                wluther

                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
                    wluther

                    fnfn & rschroeder

                    Like this, yes?

                     

                    oriondemo_udt-001.PNG oriondemo_udt-002.PNG

                      • Re: port count
                        rschroeder

                        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
                            wluther

                            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
                                cahunt

                                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
                                wluther

                                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 Vispetto

                          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

                          1 of 1 people found this helpful
                            • Re: port count
                              rschroeder

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

                               

                              Capture.JPG

                              • Re: port count
                                cahunt

                                is this reliant on you having all interfaces monitored?

                                  • Re: port count
                                    wluther

                                    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 Vispetto

                                      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
                                          wluther

                                          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
                                              Tony Vispetto

                                              That's correct.

                                              • Re: port count
                                                Tony Vispetto

                                                You 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

                                                1 of 1 people found this helpful
                                                  • Re: port count
                                                    fnfn

                                                    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
                                              sja

                                              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
                                              wluther

                                              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

                                              • Re: port count
                                                cjfranca

                                                Look this report