There are two scripts below. This is if you’re in an environment where you MAC Addresses and ARP table are on two different switches. The first scrtip is the MAC Address collector. The second script is the ARP lookup based off the MAC Addresses from the 1st script.
MAC COLLECTOR
# Author: Kerry Cordero
# Version: 1.0.0
# Description: This script will connect to the switch that has the MAC Addresses. It will export the MAC Addresses and other data to an excel spreadsheet for the next part. Part 2 is to run, arp_lookup.py.
# mac_collector.py
from netmiko import ConnectHandler
import re
import pandas as pd
import getpass
def get_switch_credentials():
"""
Prompt for switch connection details
"""
print("\nEnter switch connection details:")
switch_ip = input("Switch IP: ")
username = input("Username: ")
password = getpass.getpass("Password: ")
enable_password = getpass.getpass("Enable Password: ")
return switch_ip, username, password, enable_password
def get_interface_descriptions(switch_connection):
"""
Get interface descriptions and return as a dictionary.
"""
output = switch_connection.send_command("show interface description")
descriptions = {}
for line in output.splitlines():
if re.match(r"^Interface", line) or re.match(r"^-+", line):
continue
parts = line.split()
if len(parts) >= 4:
interface = parts[0]
description = ' '.join(parts[3:])
descriptions[interface] = description
return descriptions
def get_mac_table(switch_connection, interface_descriptions):
"""
Get MAC address table for physical interfaces (Gi and Te ports only).
"""
output = switch_connection.send_command("show mac address-table")
output_te = switch_connection.send_command("show mac address-table | include Te")
mac_table = []
# Process regular output for Gi interfaces
for line in output.splitlines():
if re.search(r"^\s*\d+", line):
columns = line.split()
if len(columns) >= 6:
vlan = columns[0]
mac = columns[1].lower()
port = columns[5]
if re.match(r"^(Gi|GigabitEthernet)", port):
description = interface_descriptions.get(port, "No description")
description = re.sub(r'up up\s*', '', description).strip()
mac_table.append({
"Interface": port,
"Description": description,
"VLAN": vlan,
"MAC Address": mac
})
# Process Te interfaces output
for line in output_te.splitlines():
if re.search(r"^\s*\d+", line):
columns = line.split()
if len(columns) >= 6:
vlan = columns[0]
mac = columns[1].lower()
port = columns[5]
if re.match(r"^(Te|TenGigabitEthernet)", port):
description = interface_descriptions.get(port, "No description")
description = re.sub(r'up up\s*', '', description).strip()
mac_table.append({
"Interface": port,
"Description": description,
"VLAN": vlan,
"MAC Address": mac
})
return mac_table
def connect_to_switch(switch_ip, username, password, enable_password):
"""
Establish connection to the switch with given credentials.
"""
switch = {
"device_type": "cisco_ios",
"host": switch_ip,
"username": username,
"password": password,
"secret": enable_password,
"timeout": 60
}
return ConnectHandler(**switch)
def print_mac_table(mac_table):
"""
Print MAC table in a formatted manner.
"""
if not mac_table:
print("\nNo MAC addresses found for Gi or Te interfaces.")
return
print("\nMAC Address Table (Gi and Te interfaces only)")
print("-" * 90)
print(f"{'Interface':<15} {'VLAN':<6} {'MAC Address':<17} {'Description':<50}")
print("-" * 90)
sorted_table = sorted(mac_table, key=lambda x: (
'1' if x['Interface'].startswith('Te') else '2',
x['Interface']
))
for entry in sorted_table:
print(f"{entry['Interface']:<15} {entry['VLAN']:<6} {entry['MAC Address']:<17} {entry['Description']:<50}")
print("-" * 90)
print(f"Total entries: {len(mac_table)}")
def export_to_excel(mac_table):
"""
Export MAC table to Excel file
"""
df = pd.DataFrame(mac_table)
filename = "old-core-port-migration.xlsx"
# Reorder columns for better readability
df = df[['Interface', 'VLAN', 'MAC Address', 'Description']]
# Export to Excel
df.to_excel(filename, index=False, sheet_name='MAC Table')
print(f"\nData exported to {filename}")
def main():
try:
# Get switch credentials
switch_ip, username, password, enable_password = get_switch_credentials()
print("\nConnecting to switch...")
connection = connect_to_switch(
switch_ip,
username,
password,
enable_password
)
print("Getting interface descriptions...")
connection.enable()
interface_descriptions = get_interface_descriptions(connection)
print("Getting MAC table...")
mac_table = get_mac_table(connection, interface_descriptions)
print("Printing results...")
print_mac_table(mac_table)
print("Exporting to Excel...")
export_to_excel(mac_table)
connection.disconnect()
print("Disconnected from switch")
except Exception as e:
print(f"\nError occurred: {str(e)}")
print(f"Error type: {type(e).__name__}")
if __name__ == "__main__":
main()
ARP LOOKUP
# Author: Kerry Cordero
# Version: 1.0.0
# Description: This script will do an ARP lookup on the switch you log into using the MAC Address from the previous script. It will then export the results to a spreadsheet.
# arp_lookup.py
from netmiko import ConnectHandler
import pandas as pd
import re
import socket
import concurrent.futures
from concurrent.futures import ThreadPoolExecutor
import getpass
def get_switch_credentials():
"""
Prompt for switch connection details
"""
print("\nEnter Nexus switch connection details:")
switch_ip = input("Switch IP: ")
username = input("Username: ")
password = getpass.getpass("Password: ")
enable_password = getpass.getpass("Enable Password: ")
return switch_ip, username, password, enable_password
def connect_to_switch(switch_ip, username, password, enable_password):
"""
Establish connection to the Nexus switch with correct device type
"""
switch = {
"device_type": "cisco_nxos",
"host": switch_ip,
"username": username,
"password": password,
"secret": enable_password,
"timeout": 60
}
return ConnectHandler(**switch)
def get_dns_name(ip):
"""
Get DNS name for an IP address with timeout handling
"""
try:
hostname = socket.gethostbyaddr(ip)[0]
return hostname
except (socket.herror, socket.gaierror, socket.timeout):
return "No DNS"
except Exception as e:
return f"DNS Error: {str(e)}"
def get_arp_table(switch_connection):
"""
Get ARP table from Nexus switch and return as dictionary with MAC as key
"""
output = switch_connection.send_command("show ip arp")
arp_dict = {}
for line in output.splitlines():
if not line.strip() or 'Address' in line or '-' in line:
continue
parts = line.split()
if len(parts) >= 4:
ip = parts[0]
mac = parts[2].lower()
if re.match(r"([0-9a-f]{4}\.){2}[0-9a-f]{4}", mac):
arp_dict[mac] = ip
return arp_dict
def update_mac_table_with_arp():
"""
Read MAC table from Excel, add IP addresses and DNS names, and save back to same file
"""
input_file = "old-core-port-migration.xlsx"
try:
df = pd.read_excel(input_file)
print(f"Successfully read {input_file}")
except FileNotFoundError:
print(f"Error: Could not find {input_file}")
return
try:
# Get switch credentials
switch_ip, username, password, enable_password = get_switch_credentials()
print("Connecting to Nexus switch for ARP lookup...")
connection = connect_to_switch(
switch_ip,
username,
password,
enable_password
)
print("Getting ARP table...")
arp_dict = get_arp_table(connection)
print("Adding IP addresses to MAC table...")
df['IP Address'] = df['MAC Address'].map(arp_dict)
print("\nPerforming DNS lookups (this may take a few minutes)...")
ips_to_lookup = df['IP Address'].dropna().unique()
dns_results = {}
with ThreadPoolExecutor(max_workers=20) as executor:
future_to_ip = {executor.submit(get_dns_name, ip): ip for ip in ips_to_lookup}
total_lookups = len(future_to_ip)
completed = 0
for future in concurrent.futures.as_completed(future_to_ip):
ip = future_to_ip[future]
try:
dns_name = future.result()
dns_results[ip] = dns_name
except Exception as e:
print(f"Error looking up {ip}: {str(e)}")
dns_results[ip] = "DNS Error"
completed += 1
if completed % 10 == 0:
print(f"Completed {completed} of {total_lookups} DNS lookups")
df['DNS Name'] = df['IP Address'].map(dns_results)
columns_order = ['Interface', 'VLAN', 'MAC Address', 'IP Address', 'DNS Name', 'Description']
df = df[columns_order]
df.to_excel(input_file, index=False, sheet_name='MAC-ARP Table')
print(f"\nUpdated data saved to {input_file}")
total_macs = len(df)
macs_with_ip = df['IP Address'].notna().sum()
macs_with_dns = df['DNS Name'].notna().sum()
print(f"\nStatistics:")
print(f"Total MAC addresses: {total_macs}")
print(f"MAC addresses with IP: {macs_with_ip}")
print(f"MAC addresses without IP: {total_macs - macs_with_ip}")
print(f"IP addresses with DNS names: {macs_with_dns}")
connection.disconnect()
print("Disconnected from switch")
except Exception as e:
print(f"\nError occurred: {str(e)}")
print(f"Error type: {type(e).__name__}")
def main():
update_mac_table_with_arp()
if __name__ == "__main__":
main()