We had several dozen access-level switches in total, each with either 24, 48, or up to 5 stack of 48 ports. In other words, we had several hundred access ports in total.
To solve this task, I've created an ansible script that saves files with the
command output "show mac address-table | exclude /1/ " to one folder.
Each file's name corresponds to the switch's name.
Why exclude /1/ ? because it is uplink ports like G1/1/1 or G1/1/2 and if we don't excude it we will have double amount of mac addresses.
Next, I combined all these files into one csv file as:
---
MAC_Address,switch,vlan,port
0000.aaaa.0012,03,31,Tw1/0/24
0000.aaaa.0012,03,11,Tw2/0/12
0000.aaaa.0012,12,11,Gi1/0/2
0000.aaaa.0012,03,11,Tw2/0/9
0000.aaaa.0012,03,11,Tw2/0/11
0000.aaaa.0012,21,44,Gi1/0/23
0000.aaaa.0012,08,11,Gi1/0/48
0000.aaaa.0012,08,71,Gi1/0/48
---
The next step involves adding a new column with the manufacturer using a Python program.
---
import csv
from mac_vendor_lookup import MacLookup
# Initialize MacLookup object
mac_lookup = MacLookup()
input_file = 'file.csv'
output_file = 'output.csv'
# Open the input CSV file to read its header and determine the fieldnames
with open(input_file, 'r') as csv_file:
reader = csv.reader(csv_file)
fieldnames = next(reader) # Read the header row
# Append 'vendor' to the existing fieldnames list
fieldnames.append('vendor')
# Open the input CSV file for reading and a new CSV file for writing
with open(input_file, 'r') as csv_file, open(output_file, 'w', newline='') as output_file:
reader = csv.DictReader(csv_file, fieldnames=fieldnames)
writer = csv.DictWriter(output_file, fieldnames=fieldnames)
writer.writeheader()
# Skip the header row
next(reader)
# Iterate through each row in the CSV file
for row in reader:
# Get MAC address from the row
mac_address = row['MAC_Address']
try:
# Lookup vendor information for the MAC address
vendor = mac_lookup.lookup(mac_address)
except Exception as e:
print(f"Error: {e}. Setting vendor to 'unknown' for MAC address: {mac_address}")
vendor = 'unknown'
# Update the 'vendor' column with the vendor information
row['vendor'] = vendor
# Write the updated row to the new CSV file
writer.writerow(row)
print("Vendor information has been added to the 'vendor' column in the CSV file.")
---
and result excel file is:
---
MAC_Address,switch,vlan,port,vendor
0000.0011.0000,03,31,Tw1/0/24,RADISYS CORPORATION
0000.0011.0000,03,11,Tw2/0/12,Rockwell Automation
0000.0011.0000,12,11,Gi1/0/2,Rockwell Automation
0000.0011.0000,03,11,Tw2/0/9,Rockwell Automation
0000.0011.0000,03,11,Tw2/0/11,Rockwell Automation
0000.0011.0000,21,44,Gi1/0/23,Rockwell Automation
0000.0011.0000,08,11,Gi1/0/48,Rockwell Automation
0000.0011.0000,08,71,Gi1/0/48,Rockwell Automation
---
result file can be easily sorted by switch or by vendor and we will have full visibility where devices connected
one of the final tasks was to make sure that for example the printers are connected to the correct VLAN and the programmable logic controllers are also connected to the correct VLAN.
