Auditing Active Switch Ports

Answered Question
Jul 20th, 2010
User Badges:

My corporate management has directed me to go into every switch on our network and audit which ports are active "connected". They want me to export this information in an Excel format by the end of the week. I know this can be done through Ciscoworks and other SNMP management tools, but unfortuntely I don't have those tools available. Is there a way within the IOS that I can export the the output of these commands to Excel?

show ip interface brief and show interface description. Thanks in advance.

Correct Answer by Iain about 6 years 11 months ago

David,


This should narrow it down a bit more.  Two options:


sh interfaces | include line protocol | [0-9].[y+w]


and (to show interfaces with input and output of "never")


sh interface | inc line protocol | Last input never, output never



This should enable you to make more intelligent decisions regarding port utilization.  We had a situation a few years back where we were able reallocate an entire 4506 because our students were using 100% wireless instead of their wired ports.  These commands showed hundreds of ports with last input of ~1 year.  If you want to get crazy with regex there are ways to further filter the displayed data.  I believe this command will show you the ports with last input of 30weeks or above.


sh interfaces | inc line protocol | [3-9].[y+w]


http://www.fileformat.info/tool/regex.htm


http://www.cisco.com/univercd/cc/td/doc/product/software/ios122/122cgcr/fdial_c/fnsprt13/dafaapre.htm



Correct Answer by Iain about 6 years 11 months ago

Once you have the output from the cli, you should be able to paste and save the date in notepad and then change the extension to .csv.  When you open for the first time in Excel, it should give you options for how you want to format data.


SIDE NOTE:  If you're planning to use this data to determine which ports can be safely disconnected, be careful.  It's easy to create more work for yourself by disconnecting users who are on vacation or systems that are powered off, etc.  I've used the "show interface" command coupled with some regular expressions to filter out the "last input", "last output" to determine the date/time the port actually last passed traffic.


Hope this helps, Iain

Correct Answer by Ganesh Hariharan about 6 years 11 months ago

My corporate management has directed me to go into every switch on our network and audit which ports are active "connected". They want me to export this information in an Excel format by the end of the week. I know this can be done through Ciscoworks and other SNMP management tools, but unfortuntely I don't have those tools available. Is there a way within the IOS that I can export the the output of these commands to Excel?

show ip interface brief and show interface description. Thanks in advance.


Use some scripting for fetching the information like show int description or show ip int brief or as suggested by Reza copy paste the output of the two command into excel.


Hope to Help !!


Ganesh.H

Correct Answer by Reza Sharifi about 6 years 11 months ago

If you just need the active (connected ports) then you can use


Management-Switch#sh ip int bri | inc up


Vlan1                      1.1.1.100       YES NVRAM  up                    up     
FastEthernet0/1            unassigned      YES unset  up                    up     
FastEthernet0/2            unassigned      YES unset  up                    up     
FastEthernet0/3            unassigned      YES unset  up                    up     
FastEthernet0/4            unassigned      YES unset  up                    up     
FastEthernet0/5            unassigned      YES unset  up                    up     
FastEthernet0/6            unassigned      YES unset  up                    up     
FastEthernet0/7            unassigned      YES unset  up                    up     
FastEthernet0/8            unassigned      YES unset  up                    up     
FastEthernet0/9            unassigned      YES unset  up                    up     
FastEthernet0/16           unassigned      YES unset  up                    up     
FastEthernet0/29           unassigned      YES unset  up                    up     
FastEthernet0/30           unassigned      YES unset  up                    up     
FastEthernet0/32           unassigned      YES unset  up                    up     
Management-Switch#


Then copy and paste into a spreadsheet


HTH

Reza

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (5 ratings)
Loading.
Correct Answer
Reza Sharifi Tue, 07/20/2010 - 19:00
User Badges:
  • Super Bronze, 10000 points or more
  • Cisco Designated VIP,

    2017 LAN

If you just need the active (connected ports) then you can use


Management-Switch#sh ip int bri | inc up


Vlan1                      1.1.1.100       YES NVRAM  up                    up     
FastEthernet0/1            unassigned      YES unset  up                    up     
FastEthernet0/2            unassigned      YES unset  up                    up     
FastEthernet0/3            unassigned      YES unset  up                    up     
FastEthernet0/4            unassigned      YES unset  up                    up     
FastEthernet0/5            unassigned      YES unset  up                    up     
FastEthernet0/6            unassigned      YES unset  up                    up     
FastEthernet0/7            unassigned      YES unset  up                    up     
FastEthernet0/8            unassigned      YES unset  up                    up     
FastEthernet0/9            unassigned      YES unset  up                    up     
FastEthernet0/16           unassigned      YES unset  up                    up     
FastEthernet0/29           unassigned      YES unset  up                    up     
FastEthernet0/30           unassigned      YES unset  up                    up     
FastEthernet0/32           unassigned      YES unset  up                    up     
Management-Switch#


Then copy and paste into a spreadsheet


HTH

Reza

Correct Answer
Ganesh Hariharan Tue, 07/20/2010 - 23:47
User Badges:
  • Purple, 4500 points or more
  • Community Spotlight Award,

    Member's Choice, February 2016

My corporate management has directed me to go into every switch on our network and audit which ports are active "connected". They want me to export this information in an Excel format by the end of the week. I know this can be done through Ciscoworks and other SNMP management tools, but unfortuntely I don't have those tools available. Is there a way within the IOS that I can export the the output of these commands to Excel?

show ip interface brief and show interface description. Thanks in advance.


Use some scripting for fetching the information like show int description or show ip int brief or as suggested by Reza copy paste the output of the two command into excel.


Hope to Help !!


Ganesh.H

Correct Answer
Iain Wed, 07/21/2010 - 07:18
User Badges:

Once you have the output from the cli, you should be able to paste and save the date in notepad and then change the extension to .csv.  When you open for the first time in Excel, it should give you options for how you want to format data.


SIDE NOTE:  If you're planning to use this data to determine which ports can be safely disconnected, be careful.  It's easy to create more work for yourself by disconnecting users who are on vacation or systems that are powered off, etc.  I've used the "show interface" command coupled with some regular expressions to filter out the "last input", "last output" to determine the date/time the port actually last passed traffic.


Hope this helps, Iain

davidhuynh5 Wed, 07/21/2010 - 14:40
User Badges:

Lain, can you give me an example of your show interface and last output command? Thanks.

Ganesh Hariharan Thu, 07/22/2010 - 04:14
User Badges:
  • Purple, 4500 points or more
  • Community Spotlight Award,

    Member's Choice, February 2016

Lain, can you give me an example of your show interface and last output command? Thanks.


Hi,


Following will be the output of show interface


DCMGTSW01>sh int gigabitEthernet 0/12
GigabitEthernet0/12 is up, line protocol is up (connected)
  Hardware is Gigabit Ethernet, address is 001e.f6d6.e40c (bia 001e.f6d6.e40c)
  MTU 1500 bytes, BW 100000 Kbit, DLY 100 usec,
     reliability 255/255, txload 1/255, rxload 1/255
  Encapsulation ARPA, loopback not set
  Keepalive set (10 sec)
  Full-duplex, 100Mb/s, media type is 10/100/1000BaseTX
  input flow-control is off, output flow-control is unsupported
  ARP type: ARPA, ARP Timeout 04:00:00
Last input never, output 00:00:00, output hang never
  Last clearing of "show interface" counters never
  Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 0
  Queueing strategy: fifo
  Output queue: 0/40 (size/max)
  5 minute input rate 4000 bits/sec, 5 packets/sec
  5 minute output rate 134000 bits/sec, 15 packets/sec

     166679788 packets input, 38493471531 bytes, 0 no buffer
     Received 2143909 broadcasts (0 multicasts)
     0 runts, 0 giants, 0 throttles
     2 input errors, 2 CRC, 0 frame, 0 overrun, 0 ignored
     0 watchdog, 4630 multicast, 0 pause input
     0 input packets with dribble condition detected
     404366999 packets output, 222734086466 bytes, 0 underruns
     0 output errors, 0 collisions, 3 interface resets
     0 babbles, 0 late collision, 0 deferred
     0 lost carrier, 0 no carrier, 0 PAUSE output
     0 output buffer failures, 0 output buffers swapped out
DCMGTSW01>


Hope to Help !!


Ganesh.H

Correct Answer
Iain Thu, 07/22/2010 - 07:09
User Badges:

David,


This should narrow it down a bit more.  Two options:


sh interfaces | include line protocol | [0-9].[y+w]


and (to show interfaces with input and output of "never")


sh interface | inc line protocol | Last input never, output never



This should enable you to make more intelligent decisions regarding port utilization.  We had a situation a few years back where we were able reallocate an entire 4506 because our students were using 100% wireless instead of their wired ports.  These commands showed hundreds of ports with last input of ~1 year.  If you want to get crazy with regex there are ways to further filter the displayed data.  I believe this command will show you the ports with last input of 30weeks or above.


sh interfaces | inc line protocol | [3-9].[y+w]


http://www.fileformat.info/tool/regex.htm


http://www.cisco.com/univercd/cc/td/doc/product/software/ios122/122cgcr/fdial_c/fnsprt13/dafaapre.htm



Actions

This Discussion