Cisco Switch Ports Utilization

Hello Forum, I am working to build a report using either NPM or NCM modules to obtain port utilization. This is for Cisco Switches.

Format would be "Switch/Node Name", "Total Ports", "Ports Used", "Ports Available".

Looking for ideas on how this can be achieved. Many thanks.

PS: We do not have UDT in the environment

Find more posts tagged with

cisco switches

switchports

portutilization

Accepted answers

dunky2k

We use the following report below to list the top 30 switches by either percentage used or percentage free.

SELECT Top 30 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' or OperStatus = '1' THEN 1 ELSE 0 END AS IsUp,

CASE WHEN OperStatus = 'Down' or OperStatus = '2' 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 'Available Ports' DESC

marlief22

Hello there,

SELECT 
    inf.Node.Caption as [Node Name], 
    COUNT(inf.InterfaceID)  as [Total Ports], 
    SUM(CASE WHEN inf.OperStatus in (2, 10) THEN 1 ELSE 0 END)  as [Free Ports], 
    SUM(CASE WHEN inf.OperStatus = 1 THEN 1 ELSE 0 END)  as [Used Ports]
FROM Orion.NPM.Interfaces inf
where 
    inf.typename = 'ethernetCsmacd' and 
    inf.AdminStatus = 1 and 
    inf.node.CustomProperties.Devicetype = 'Switch' and --remove line if you don't want to use this custom property
    inf.node.Vendor = 'Cisco'
GROUP BY 
    inf.Node.Caption

The above is what I would use, this is a SWQL query rather than SQL so you don't have to use any JOIN commands. It uses the property 'ethernetCsmacd' to limit to only switch ports, and then limits to Cisco vendors, and a custom property called 'DeviceType' that you may have to create this list of interfaces. It will look like the following:

Have a try with it and let me know how you get on.

Kind regards,

Marlie Fancourt | SolarWinds Pre-Sales Manager

Prosperon Networks | SolarWinds Partner since 2006

If this helps answer your question please mark my answer as confirmed to help other users, thank you!

All comments

dunky2k

We use the following report below to list the top 30 switches by either percentage used or percentage free.

SELECT Top 30 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' or OperStatus = '1' THEN 1 ELSE 0 END AS IsUp,

CASE WHEN OperStatus = 'Down' or OperStatus = '2' 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 'Available Ports' DESC

marlief22

Hello there,

SELECT 
    inf.Node.Caption as [Node Name], 
    COUNT(inf.InterfaceID)  as [Total Ports], 
    SUM(CASE WHEN inf.OperStatus in (2, 10) THEN 1 ELSE 0 END)  as [Free Ports], 
    SUM(CASE WHEN inf.OperStatus = 1 THEN 1 ELSE 0 END)  as [Used Ports]
FROM Orion.NPM.Interfaces inf
where 
    inf.typename = 'ethernetCsmacd' and 
    inf.AdminStatus = 1 and 
    inf.node.CustomProperties.Devicetype = 'Switch' and --remove line if you don't want to use this custom property
    inf.node.Vendor = 'Cisco'
GROUP BY 
    inf.Node.Caption

Have a try with it and let me know how you get on.

Kind regards,

Marlie Fancourt | SolarWinds Pre-Sales Manager

Prosperon Networks | SolarWinds Partner since 2006

If this helps answer your question please mark my answer as confirmed to help other users, thank you!

sunvad

Thank you @dunky2k and @marlief22

Quick question pls - Is the query fetching directly from the Switch? Reason I ask is because we have a case where not all interfaces were checked (under list resources) when a particular node was added, probably because it was inactive/not in use.

If the query only works for interfaces currently added in SolarWinds then there would be a likely discrepancy.

I was wondering if NCM has the capability to directly get the data from the Switch, regardless if the interfaces exist in SolarWinds.

metzler

It's pulling NPM node data so it would only work if the interfaces are in NPM. I'm not confident you can report on utilization without the interfaces added. My reports do require they be added. I've added all interfaces to NPM and changed access port and ports we don't care that much about to show as unplugged instead of down to avoid warnings, etc.

dunky2k

Sent you a PM with screenshots, our environment also doesn't have all the interfaces listed, but the report does show the correct number of physical ports in total.

I would attach it here, but i cant seem to be able to find a way to upload any pictures.

sunvad

Late update

And you're right. I have tested your query and it does list the correct number of ports. Works perfectly for me.

Thank you.