Using xlsx for netmiko session

Question

I want to use both user input (for username/pw) and an excel file (for ip/device_type) for a netmiko connecthandler session for every device in the excel file reusing the account info provided. Right now the user input is static but I will get to that later. How do I build the connecthandler?

from netmiko import ConnectHandler import pandas as pd

accountinfo = { 'username': 'admin', 'password': 'password', 'secret': 'cisco', }

devices = pd.read_excel('Hosts.xlsx', usecols='b,e')

print (devices)

IP Device Type

0 192.168.0.118 cisco_ios

Answer 1

To achieve your goal, you can iterate over the rows of the Excel file and use the information from each row to establish a Netmiko session with the corresponding device. Example of how you can build the ConnectHandler

import pandas as pd
from netmiko import ConnectHandler

# Read data from the Excel file
accountinfo = {"username": "admin", "password": "password", "secret": "cisco"}
devices = pd.read_excel("Hosts.xlsx")
print(devices)
#                IP Device Type
# 0  192.168.0.118    cisco_ios

# Iterate over the devices in the DataFrame and establish SSH connections
for index, row in devices.iterrows():
    device = {"ip": row["IP"], "device_type": row["Device Type"]}
    device.update(accountinfo)
    ssh = ConnectHandler(**device)
    try:
        output = ssh.send_command("show version")
        print(device["ip"])
        print(output)
        # 192.168.0.118
        # Cisco IOS Software, C2960X Software (C2960X-UNIVERSALK9-M), Version 15.2(4)E10, ...
        ssh.disconnect()
    except Exception as ex:
        print(f"{type(ex).__name__}: {ex}")