Auditing Active Switch Ports

[davidhuynh5]

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.

[Reza Sharifi · Accepted solution]

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

[Ganesh Hariharan · Accepted solution]

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

[Iain · Accepted solution]

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]

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

[Ganesh Hariharan]

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

[Iain · Accepted solution]

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

http://aconaway.com/2008/04/13/using-the-pipe-in-ios/

http://www.nil.com/ipcorner/EnhanceIOSUI/