In Part1 of this series of network automation, we saw how to read device data from csv files and build a dictionary of that data so that we can have netmiko leverage that directly from the csv sheet rather than building the dictionary manually for each device.
Now, we are going to see how to fetch the data you need to from devices, parse them, and put them into a csv sheet for creating databases which have become a really important part of network automation, and its called auditing the devices for the information you need and preparing business-ready insights into the data at a single glance.
Step1:-
- Establish connection to network devices
- Parse output of show version from the devices
Step2:-
Once we have the required data, let’s see how we can put all of that into a CSV file. Assume we need to gather hostname, software version, model, etc into each row into a CSV file.
from netmiko import ConnectHandler
import csv
import re
with open('cisco_always_on_devnet_sanbox_lab.csv', encoding='utf-8-sig') as f:
for row in csv.DictReader(f):
device_dict = {
'host' : row['HOST'],
'device_type' : row['device_type'],
'username' : row['USERNAME'],
'password' : row['PASSWORD'],
'secret' : row['SECRET'],
}
net_connect = ConnectHandler(**device_dict)
output = net_connect.send_command('show version')
device = device_dict['host']
version = re.findall('Version (\S+)$', output, re.MULTILINE|re.IGNORECASE)
uptime = re.findall('uptime is (.+?)$', output, re.M|re.IGNORECASE)
print(device, version, uptime)
╰─ python3 script3.py ─╯
sandbox-iosxe-latest-1.cisco.com ['17.03.01a'] ['49 minutes']
sandbox-iosxr-1.cisco.com ['6.5.3'] ['1 week 5 days 4 hours 40 minutes']
sandbox-nxos-1.cisco.com ['9.3(3)'] ['7 day(s), 4 hour(s), 48 minute(s), 12 second(s)']With the above code, we have been able to
- Read csv files
- Connect to devices mentioned in the csv file
- Extract output of show version from all those devices
- parse the output to extract version, uptime
Step3:- Create a datastructure that we can use to write to CSV file using python.
- Method1:- Creating a list of lists for each list is 1 row in the csv file you want to output. Notice the difference between writerow and writerows. writerow will only write 1 list to a row but if you have a nested list, then writerows will write that nested list to multiple lines itself.
from netmiko import ConnectHandler
import csv
import re
device_info = []
with open('cisco_always_on_devnet_sanbox_lab.csv', encoding='utf-8-sig') as f:
for row in csv.DictReader(f):
device_dict = {
'host' : row['HOST'],
'device_type' : row['device_type'],
'username' : row['USERNAME'],
'password' : row['PASSWORD'],
'secret' : row['SECRET'],
}
net_connect = ConnectHandler(**device_dict)
output = net_connect.send_command('show version')
device = device_dict['host']
version = re.findall('Version (\S+)$', output, re.MULTILINE|re.IGNORECASE)[0]
uptime = re.findall('uptime is (.+?)$', output, re.M|re.IGNORECASE)[0]
device_info.append([device, version,uptime])
header = ['Device Name', 'Version', 'Uptime']
with open('output.csv', 'w') as f:
csvwriter = csv.writer(f)
csvwriter.writerow(header)
csvwriter.writerows(device_info)- Method2:- You could initialize the output list with header in the start
from netmiko import ConnectHandler
import csv
import re
device_info = [['Device Name', 'Version', 'Uptime']] >>>>> header initialized here.
with open('cisco_always_on_devnet_sanbox_lab.csv', encoding='utf-8-sig') as f:
for row in csv.DictReader(f):
device_dict = {
'host' : row['HOST'],
'device_type' : row['device_type'],
'username' : row['USERNAME'],
'password' : row['PASSWORD'],
'secret' : row['SECRET'],
}
net_connect = ConnectHandler(**device_dict)
output = net_connect.send_command('show version')
device = device_dict['host']
version = re.findall('Version (\S+)$', output, re.MULTILINE|re.IGNORECASE)[0]
uptime = re.findall('uptime is (.+?)$', output, re.M|re.IGNORECASE)[0]
device_info.append([device, version,uptime])
with open('output.csv', 'w') as f:
csvwriter = csv.writer(f)
csvwriter.writerows(device_info) >>>> no need to use writerow anymore.- Method3:- Instead of using csvwriter class, we can leverage dictwriter class of csv module just like we did in the case of reading the csv file.
from netmiko import ConnectHandler
import csv
import re
device_info = []
with open('cisco_always_on_devnet_sanbox_lab.csv', encoding='utf-8-sig') as f:
for row in csv.DictReader(f):
device_dict = {
'host' : row['HOST'],
'device_type' : row['device_type'],
'username' : row['USERNAME'],
'password' : row['PASSWORD'],
'secret' : row['SECRET'],
}
net_connect = ConnectHandler(**device_dict)
output = net_connect.send_command('show version')
device = device_dict['host']
version = re.findall('Version (\S+)$', output, re.MULTILINE|re.IGNORECASE)[0]
uptime = re.findall('uptime is (.+?)$', output, re.M|re.IGNORECASE)[0]
device_info.append({
'Device Name' : device,
'Version' : version,
'Uptime' : uptime
})
with open('output.csv', 'w') as f:
csvwriter = csv.DictWriter(f, fieldnames = ['Device Name', 'Version', 'Uptime'])
csvwriter.writerows(device_info)
Part3 of this series
3 thoughts on “How to read write csv with python for Network Automation Part 2”