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
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.
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.