Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Report on ports used and unused

I’ve been asked for a report on how many ports we have on our network and how many are used. Without going to each device and running a sh int status command I’m wondering if there are any forms anyone might have for NPM

0 Kudos
17 Replies

I use this method to discover ports that have not been used recently, and then I decide which can be unpatched and repurposed:

0 Kudos
Level 10

Do you have NCM? I can see that if you do an SWQL query using NCM.Nodes and NCM.Interfaces you could pull a report by device name with a count on admin status Up/down/total per device.

How does that scale to 1000 switches?   Or can you query all at once and see the output in one report?

0 Kudos

This is something I pulled together real quick using SWQL to display "Device Name, Device IP, then "interface" by Admin Up/Down and Oper Status Up/Down along with Total available. I added a where statement to only return "physical interfaces" because I believe that's what the original intent was of the question on this string. So a device like a Cisco Catalyst 3560 w/ 48 ports would show as 52 interfaces (48 ports + 4 SFP). For scaling the way it's written right now it will return every device in NCM.Nodes. In some enviornments that could mean more than network devices.

rschroeder​ this returned 1523 rows for me so scale wise it shouldn't be an issue. As long as all the devices you're looking for information on are managed/in NCM. To clarify this is NCM not NPM and it will return all ports/interfaces on the device even those not discovered by NPM/NCM. In my environment we only discover trunk ports and/or "important" ports so using NPM wouldn't return the same results as it would only be able to return discovered ports/interfaces.


  NodeCaption AS [Device Name],

  AgentIP AS [Device IP],


            CASE WHEN n.Interfaces.AdminStatus = 'Up'

            THEN 1 ELSE NULL END


                 AS [Admin Up],


            CASE WHEN n.Interfaces.OperStatus = 'UP'

            THEN 1 ELSE NULL END


                 AS [Oper Up],


            CASE WHEN n.Interfaces.AdminStatus = 'Down'

            THEN 1 ELSE NULL END


                 AS [Admin Down],


            CASE WHEN n.Interfaces.OperStatus = 'Down'

            THEN 1 ELSE NULL END


                 AS [Oper Down],




                 AS [Total]


  NCM.Nodes n


  n.Interfaces.PhysicalInterface = 'Y'


  NodeCaption, AgentIP




Thanks for sharing query , this almost fulfill my requirement . But we only need to have data about interfaces which are in NPM . This query seems fetching data for all interfaces in NCM not in NPM , so it might possible few of them must exists in NPM and few of them are not in NPM . For this what changes should i do in this SWQL query .Please help 

0 Kudos

This is not working for me I am getting error in Running the query as per screen

Requesting you to help

hope this query works in NCM 8.0 and NPM 12.5 Orion Platform 2019

0 Kudos

pparsaie​  Is there line I can add, or an option I can enable, in your report that will display the sum of all ports, for a "Total Ports Across All Nodes" entry?

Obviously I can export it to Excel and run the Summary function on a one-time or as-needed basis, but it would be an attractive feature to show Total Ports on ALL nodes instead of only one EACH node.

Swift packets!

Rick Schroeder

0 Kudos

This helps greatly as has got me pointed in the right direction.   Although I am horrible with SWQL, was wondering if there is a way to get the total ports like above but base it on a custom property of the nodes, So lets say all my network nodes have a custom property call Allocation ID, is there way to pull total port counts of the sum of all the nodes based on the Custom Property (Allocation ID) that is assigned to them?

An example would be something like:

AllocationID Name                        Admin Up                    Oper Up               Admin Down                    Oper Down               Total    

Boston                                           52                                18                         0                                      34                             52

New York                                       23                                11                         5                                      17                              28

0 Kudos

Would this work under UDT as I tried but unable to get desired results ?

Port-capacity-thwack-111126 - randomised.JPG

0 Kudos

usmanshaikh​ - try changing the 'Up' and 'Down' to 1 and 2 (WHEN ncmi.OperStatus = '1')

NodeID, Caption,

(*) AS "Total Ports", SUM (IsUp) AS "Used Ports", SUM (IsDown) AS "Oper Down Ports", SUM (IsAdminUp) AS "Admin Up Ports", SUM (IsAdminDown) AS "Admin Down Ports"


ND.Caption, ND.NodeID,

WHEN ncmi.OperStatus = '1' THEN 1 ELSE 0 END AS IsUp,

WHEN ncmi.OperStatus = '2' THEN 1 ELSE 0 END AS IsDown,

WHEN ncmi.AdminStatus = '1' THEN 1 ELSE 0 END AS IsAdminUp,

WHEN ncmi.AdminStatus = '2' THEN 1 ELSE 0 END AS IsAdminDown


JOIN NodesData ND ON (NCMNP.CoreNodeID = ND.NodeID)



BY NodeID, Caption

BY Caption ASC

I gave your process a shot in SDK, but it came back with this error:


Is that a SWQL query, and if so, I'm not certain how to correct the error based on the screen shot above.  What are your recommendations?

0 Kudos


where i can run this swsql query in ncm.

please provide the step

0 Kudos

User Device Tracker also contains port count information and port-up and port-down information.  It's not as useful to me for repurposing ports on an individual switch basis as my custom report is, but it does serve a purpose for higher-up planning.

How to create a report displaying the Last Time Data was Transmitted or Received on a Switch Port


You may be able to copy & edit either of these two reports to get the information you desire.

You can create a custom report that uses SQL or SWQL input in its advanced Database Query area by going to

  1. Reports > Manage Reports > Create New Report
  2. Click the radio button to select a Custom Table
  3. Click the drop down for Selection Method and click on Advanced Database Query (SQL,SQWL)
  4. Paste your SQL or SWQL query in there
  5. Continue to build your report, then test and modify as needed.

you can create the report that I use by following the instructions here:

How to create a report displaying the Last Time Data was Transmitted or Received on a Switch Port

If you have the SDK, you may be able to find a pre-created query that accomplishes what you need; I haven't found one there yet.  sqlrockstar​ might be a great resource for discovering what SWQL query would provide the desired information.

As I understand this thread, the original request is to find a way to:

  1. List total network ports on the network
  2. Show which ones have link and which have no link

I think it would be useful to include how long any port without link has been down.

0 Kudos

I like the concise output of your report, but it doesn't provide the information I needed.  I was searching for a method of discovering how long inactive ports have been inactive.  The sole focus is discovering the length of time that inactive ports have stayed inactive, and sorting them by duration of inactivity.

The goal is to discover ports which have been inactive for a very long time, and then make them available for new devices by unpatching them.

The method I created here, How to create a report displaying the Last Time Data was Transmitted or Received on a Switch Port , does exactly what I need on a per-switch basis.

It would be interesting to see a report that provides the same inactive port duration for all switches in an enterprise . . .    It would have to separate out nodes by some criteria (node name, preferably), and scale to hundreds or thousands of switches, each of which could have several hundred ports.  But for now, I'm satisfied with the method I built that collects the duration of port inactivity on a single switch.

0 Kudos

Hello pparsaie

can you please tell me where can i run this query to get the port details.

I am confused.

0 Kudos
Level 12

Not for NPM.  Within NPM you specify which ports are monitored.  For example, on a 24 port access switch, we typically monitor the trunk ports.  That's just 2 ports on the switch.  By the way, you can designate the other ports as plugged/unplugged and get a capacity report that way, but this is not typically done.

For knowing how many ports in the network are active, the User Device Tracker (UDT) module would be a better fit. The UDT module will monitor all ports on switch and keep track of MAC level information and will provide a port capacity report.

I would recommend you install the evaluation version of UDT to see if it gets you the functionality you need.



0 Kudos