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.

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.

Regards,

Anil Singh

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

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

  • Dear ,

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

    very grateful!!

  • 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 (1.3.6.1.2.1.2.1) from the node so it can be referenced in the report. jangliss‌?

  • 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

  • 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.
    OrionReport-InterfaceCountSetup.png
    Here is an example of the quick and dirty report:
    OrionReport-Report.png