7 Replies Latest reply on May 6, 2015 10:42 AM by anil.singh

    Interface Count on each node


      Hi All,


      I'm looking for a report that can count the interfaces that are being monitored under each node in NPM 10.7.





      Anil Singh

        • Re: Interface Count on each node
          Craig Norborg

          You could probably do this in Report Writer too quite easily, I choose to use SWQL in this instance...


          SELECT N.Caption, COUNT(I.InterfaceID) AS Num_Ints

          FROM Orion.Nodes N JOIN Orion.NPM.Interfaces I ON (I.NodeID = N.NodeID)

          GROUP BY N.Caption

          ORDER BY N.Caption


          You should be able to add this to any page by customizing the page, adding the Custom Query resource and pasting this in as a query.  Or, you could just download the SDK and run it in there.


          If you change the second line to just "FROM Nodes N JOIN Interface I ON (I.NodeID = N.NodeID)" this changes from being a SWQL query to being a SQL query and you can use it anywhere that wants that also...

            • Re: Interface Count on each node
              Steven Klassen

              The nice thing about using SWQL is we don't have to rely on JOINs if there's a linking property.


              SELECT N.Caption, COUNT(N.Interfaces.InterfaceID) AS Num_Ints

              FROM Orion.Nodes N

              GROUP BY N.Caption

              ORDER BY N.Caption


              This also has the bug undocumented feature of behaving like a LEFT JOIN, including a 0 count for interfaces on nodes that have none.

              • Re: Interface Count on each node

                Dear ,

                How you could modify this query to list the monitored interfaces / total interfaces in another column ?


                very grateful!!

                  • Re: Interface Count on each node
                    Steven Klassen

                    The Orion.NPM.Interfaces table only has information about monitored interfaces and I looked around a bit and don't see any mention of a total interface number. So unless someone else can correct me I don't think it's possible without creating a custom poller to go pull ifNumber ( from the node so it can be referenced in the report. jangliss?

                    • Re: Interface Count on each node
                      Craig Norborg

                      mrxinu is partially right, if all you have is NPM, you are SOL.  Nothing else you can do that I can think of...


                      However, if you have NCM, it does inventory the devices, so lets see what we can do.  First off, we're going to leverage mrxinu SWQL that he gave us above that so nicely illustrated how to you the linking properties.  However, once we use these it's a bit more difficult to translate this query into an SQL query since the standard SQL doesn't have these.


                      Just like NPM, NCM has it's own Interfaces table, only this one is an inventory of the entire device rather than just the monitored interfaces.  I wouldn't be surprised if at some point they collapse these two into one, but for now they're separate.   You might be able to get this a bit easier with a certain type of JOIN, but I always have problems figuring them out quite right, so I'll stick with a nested query within my query.   You can see that the basics of the query are the same, but I added another nested query to go out to the "Cirrus.Interfaces" table to see how many interfaces it has for the same node ID.   NCM uses a different NodeID than NPM, but the main NCM table has both it's own "NodeID", which we don't want,  and what we want, the "CoreNodeID" that corresponds to the "NodeID" from NPM.   Since we're using the linking property to get this "CoreNodeID" it makes our query a bit simpler (no additional JOIN's needed) but also has the side effects noted above.


                      SELECT N.Caption, COUNT(N.Interfaces.InterfaceID) AS Num_Ints, (SELECT COUNT(I.InterfaceID) AS Num_Ttl FROM Cirrus.Interfaces I WHERE (N.NodeID = I.Node.CoreNodeID)) AS Num_Ttl

                      FROM Orion.Nodes N 

                      GROUP BY N.Caption, N.NodeID

                      ORDER BY N.Caption


                      The first thing you notice when you run this is that the # of total interfaces is probably greater than what you thought it was.  However, it is reporting the TRUE number of total interfaces, some of which we might forget about.  So, on an 881 router, which you might think has 8 interfaces from doing a "show int desc" or something similar (Fa0, Fa1, Fa2, Fa3, Fa4, Vl1, Wlan-G0, wlan-ap0) comes up with a count of 9 from NPM.   The way you can see what interfaces are being counted is to do a "show snmp mib ifmib ifindex" as shown below.   Note that there is a "Null0" that wasn't shown in other "show int" outputs!


                      #show snmp mib ifmib ifindex

                      FastEthernet4: Ifindex = 6

                      FastEthernet0: Ifindex = 2

                      FastEthernet2: Ifindex = 4

                      Null0: Ifindex = 8

                      Wlan-GigabitEthernet0: Ifindex = 7

                      wlan-ap0: Ifindex = 1

                      Vlan1: Ifindex = 9

                      FastEthernet1: Ifindex = 3

                      FastEthernet3: Ifindex = 5

                    • Re: Interface Count on each node
                      Correct, this would be very, very easy to do in the web-based report writer (easier for people like myself!)
                      Just create a custom table report and add a column for the Interface Name. In the advanced section choose to aggregate the data by count and you're done. You can do the normal sort-bys and such to get there you need.
                      Here is an example of the quick and dirty report: