port count

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

-Will

That's correct.

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