Network Automation In Action - Cisco + Netmiko + Pandas + CSV

Network Automation In Action - Cisco + Netmiko + Pandas + CSV
In: NetDevOps, Python, Cisco

Welcome to our 'Network Automation in Action' series! In today's post, I'm taking you through a recent challenge I faced. My task was to identify all the permanently connected devices across a network of over 20 access switches.

I was specifically looking for end devices that are always on the network – not the laptops that our team plugs in daily.

How did I do it without spending countless hours manually checking each switch? That's where the magic of network automation comes in, combining the power of Netmiko, Pandas, and CSV. Let's dive into how this mix made my job a whole lot easier!

If you are not familiar with Python or Network Automation in general, please check out my other blog posts below.

The Attributes I'm After

In this task, my aim was to create a structured table that captures essential network details. I needed it to include the switch name, the port where a device is connected, port description, VLAN, Mac address, IP address, hostname, and, if possible, the User-ID.

Those of you familiar with Cisco will know that getting this information requires executing several commands. For instance, you can use show interface status to identify the connected ports and show mac address table to get the MAC address of that port.

The default gateway might be on a different switch/router. To work around this, you can look at the ARP table to retrieve the IP address. As for finding the hostname, you can do a reverse DNS lookup. Now, for the User-ID – you can check the Palo Alto traffic logs.

Let's be real, doing all of this manually would be a massive task. It's not just time-consuming; it's also prone to errors. And imagine having to do it all over again for some reason. That's why I turned to automation – to make this process efficient and repeatable without the headache.

If you want to get the user-id, you will need to export the traffic logs from the firewall as a CSV file and keep it in the same directory as the Python script. The script automatically maps the IP address to the username from the traffic log

Python Script

In this section, I'll outline the logic behind the Python script I used for this task. The script combines various tools and libraries to automate the process of gathering network device information.

from netmiko import ConnectHandler
import os
import socket
import csv
import pandas as pd

pd.set_option('display.max_columns', None)
username = 'username'
password = os.environ.get('passwd')

switches = {
    "switch-01": "",
    "switch-02": "",
    "switch-03": "",
    "switch-04": "",
    "switch-05": "",

gateway = {
    "device_type": "cisco_ios",
    "host": '',
    "username": username,
    "password": password

arp_conn = ConnectHandler(**gateway)
arp_table = arp_conn.send_command('show ip arp', use_textfsm=True)

def ip_mac(arp_table, mac):
    for entry in arp_table:
        if entry['mac'] == mac:
            return entry['address']
        return 'N/A'

def ip_host(ip):
        host_info = socket.gethostbyaddr(ip)
        hostname = host_info[0]
        return hostname
        return 'N/A'

def user_id(df, ip):
        user = df[df['Source address'] == ip]['Source User'].iloc[0]
        return user
        return 'N/A'
device_list = []
for hostname,ip in switches.items():
    device = {
        "device_type": "cisco_ios",
        "host": hostname,
        "username": username,
        "password": password, 
        "secret": password

with open('ports.csv', 'w') as w_file:
    writer = csv.writer(w_file)
    writer.writerow(['Switch', 'Port', 'VLAN', 'Description', 'Mac', 'IP', 'User', 'Hostname'])

df = pd.read_csv('log.csv')
df = df[['Source address', 'Source User']]

for each_device in device_list:
    connection = ConnectHandler(**each_device)
    int_status = connection.send_command('show interfaces status', use_textfsm=True)
    mac_table = connection.send_command('show mac address-table', use_textfsm=True)
    port_details = []
    for interface in int_status:
        if interface['status'] == 'connected' and interface['vlan'] != 'trunk' and 'Gi' in interface['port']:
            for interf in mac_table:
                if interf['destination_port'][0] == interface['port']:
                    mac = interf['destination_address']
                    ip = ip_mac(arp_table, mac)
                    hostname = ip_host(ip)
                    user = user_id(df, ip)

                    with open('ports.csv', 'a') as w_file:
                        writer = csv.writer(w_file)
                        writer.writerow([each_device['host'][:-10], interface['port'], interf['vlan'], interface['name'], mac, ip, user, hostname])

Firstly, I defined all the access switches, along with the username and password for accessing them. Additionally, I specified the gateway to retrieve the ARP table, crucial for mapping MAC addresses to IP addresses.

I then used Netmiko, to establish SSH connections to the devices. Using Netmiko, I executed commands like show interfaces status and show mac address-table, and then leveraged TextFSM to parse these command outputs into a structured format.

An important step involved the traffic logs. I had previously exported these logs as a CSV file. Here, Pandas played a key role. Using this library, I cleaned up the data, focusing only on the 'Source address' and 'Source User' columns. This was essential for associating each IP address with a user.

My script also included several conditions to refine the data collection. For example, I filtered out trunk ports since they're primarily used for uplinks, and focused solely on access ports. This helped in identifying only the relevant network devices.

Lastly, I also used Python's socket module for reverse DNS lookups. This allowed me to resolve IP addresses back to their hostnames, adding another layer of detail to the data I was compiling. This is what I ended up with after running the script, exactly what I needed.


In conclusion, as we've seen, Python proves to be an incredibly useful tool for automating manual tasks, especially in the world of network engineering. By using network automation, we can streamline processes that would traditionally take hours or even days, reducing them to just a few minutes of scripting.

The real beauty of Python lies in its simplicity and the vast array of libraries available, like Pandas and Netmiko, which make it possible to handle complex tasks with relative ease.

Written by
Suresh Vina
Tech enthusiast sharing Networking, Cloud & Automation insights. Join me in a welcoming space to learn & grow with simplicity and practicality.
More from Packetswitch
Table of Contents
Great! You’ve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to Packetswitch.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.