06-06-2020 06:38 PM
Hi,
I am using Netmiko and textfsm to get an output of all the interface status and the mac address table. The final aim is to combine the interface status and mac address table output and map all the mac addresses against an interface in one excel cell using openpyxl. The script works if there is a single mac address against an interface but for an interface with multiple mac addresses the script overwrites the previous mac addresses and put the last one in the excel cell.
Appreciate any assistance.
06-06-2020 08:13 PM
06-09-2020 06:31 AM
Hi, apologies for the delay. Following is current code.
from netmiko import ConnectHandler,ssh_exception
from getpass import getpass
import openpyxl
from datetime import datetime
from time import gmtime, strftime
import os
import pprint
#Load the Switch inventory file which has switchname in column 1 and IP address in Column 2. Store the file in same directory where the script is or else define the path using oscwd
wbswitches = openpyxl.load_workbook("switchenv1.xlsx")
ws = wbswitches.active
max_row = ws.max_row
switches = []
for row in range (2, max_row+1):
switch = dict()
switch ['region'] = ws.cell(row= row, column = 1).value
switch ['country'] = ws.cell(row= row, column = 2).value
switch ['site_name'] = ws.cell(row= row, column = 3).value
switch ['device_serial_number'] = ws.cell(row= row, column = 7).value
switch ['site_address'] = ws.cell(row= row, column = 8).value
switch ['ip_address'] = ws.cell(row= row, column = 5).value
switch ['device_name'] = ws.cell(row= row, column = 4).value
switches.append(switch)
wbswitches.close()
user = input('username:')
passwd = getpass()
#Load the file portcounters to which the output will be written.Store the file in same directory where the script is or else define the path using oscwd
actual_time = strftime("%Y-%m-%d_%H-%M-%S")
logfile_open = open("dot1xLog - " + str(actual_time) + ".txt", "a")
start_time = datetime.now()
for switch in switches:
switchname = switch ['device_name']
switchaddress = switch ['ip_address']
switchregion = switch ['region']
switchcountry = switch ['country']
switchsitename = switch ['site_name']
switchserialnumber = switch ['device_serial_number']
switchsiteaddress = switch ['site_address']
start_time1 = datetime.now()
print(start_time1,"Collecting",switchname,"@",switchaddress)
print(start_time1,"Collecting",switchname,"@",switchaddress, file=logfile_open)
if switchname == None:
switchname = "Blank"
print (switchname)
swcon = {
'device_type': "cisco_ios",
'ip': switchaddress,
'username': user,
'password': passwd,
'secret': passwd,
}
wbwr = openpyxl.load_workbook("dot1x.xlsx")
ws = wbwr.active
ws.title = "Interface Type"
x=1
mc = ws.max_column
max_row = ws.max_row
for row in range(2,ws.max_row):
if ws.cell(row=row, column=1).value == switchname:
x=row-1
print("match")
print(x)
break
else:
x=max_row
print("no match")
print(x)
net_connect = ConnectHandler(**swcon)
net_connect.enable()
output = net_connect.send_command ("show interfaces status", use_textfsm = True)
output2 = net_connect.send_command ("show mac address-table", use_textfsm = True)
end_time = datetime.now()
print(end_time,"Completed",switchname,"@",switchaddress)
print(end_time,"Completed",switchname,"@",switchaddress, file=logfile_open)
for interface in output:
for macaddress in output2:
if macaddress['destination_port'] == interface['port']:
x=x+1
ws.cell(row = x, column = 1, value=switchname)
ws.cell(row = x, column = 2, value=switchaddress)
ws.cell(row = x, column = 3, value=switchregion)
ws.cell(row = x, column = 4, value=switchcountry)
ws.cell(row = x, column = 5, value=switchsitename)
ws.cell(row = x, column = 6, value=switchsiteaddress)
ws.cell(row = x, column = 7, value=switchserialnumber)
ws.cell(row = x, column = 8, value=interface['port'])
ws.cell(row = x, column = 9, value=interface['vlan'])
ws.cell(row = x, column = 11, value=macaddress['destination_address'])
net_connect.disconnect()
wbwr.save('dot1x.xlsx')
logfile_open.close()
end_time = datetime.now()
The above writes to the excel file but only the last MAC address against an interface. The objective is to put all the MAC addresses against an interface in column 11.
06-10-2020 12:27 AM
output = net_connect.send_command ("show interfaces status", use_textfsm = True) output2 = net_connect.send_command ("show mac address-table", use_textfsm = True) end_time = datetime.now() print(end_time,"Completed",switchname,"@",switchaddress) print(end_time,"Completed",switchname,"@",switchaddress, file=logfile_open) for interface in output: for macaddress in output2: if macaddress['destination_port'] == interface['port']: x=x+1 ws.cell(row = x, column = 1, value=switchname) ws.cell(row = x, column = 2, value=switchaddress) ws.cell(row = x, column = 3, value=switchregion) ws.cell(row = x, column = 4, value=switchcountry) ws.cell(row = x, column = 5, value=switchsitename) ws.cell(row = x, column = 6, value=switchsiteaddress) ws.cell(row = x, column = 7, value=switchserialnumber) ws.cell(row = x, column = 8, value=interface['port']) ws.cell(row = x, column = 9, value=interface['vlan']) ws.cell(row = x, column = 11, value=macaddress['destination_address'])
Make sure the intention (space) in the above is correct. Your script have the correct logic to list out all the mac address per interfaces. To debug on the issue, you are advised to confirm the library ntc-template would return you a correct data.
Try to add the below printing for troubleshooting
output = net_connect.send_command ("show interfaces status", use_textfsm = True) output2 = net_connect.send_command ("show mac address-table", use_textfsm = True) print(output2) # Make sure you got the data return to you have all the mac addresses (not only 1 mac address per port)
# Please also post the result here (with masking the MAC addresses for confidential purpose)
end_time = datetime.now() print(end_time,"Completed",switchname,"@",switchaddress) print(end_time,"Completed",switchname,"@",switchaddress, file=logfile_open)
I have seen that there is a BUG fix for ntc-template on GitHub since 2 months ago. You are advised to update your ntc-template library.
06-07-2020 12:53 AM
here is the git source you can play with the script how you like your desired output.
06-09-2020 06:32 AM
Thank you Balaji. I am a newbie at it. And looking at the code I can not find anything which provides what I need.
06-09-2020 11:19 AM - edited 06-09-2020 11:20 AM
Trying you understand the requirement -
you want to learn Python code which can meet the requirement
or you looking on shelf script you run for your requirement?
06-07-2020 03:01 AM - edited 06-07-2020 03:02 AM
Hi @pfillips11
Personally I prefer using pandas for writing to xls. Feels more natural and straight forward. Here is an example of your scenario (mac table to xls):
import pandas as pd
from netmiko import Netmiko
from getpass import getpass
password = getpass()
net_connect = Netmiko(host='172.16.10.10', username='admin', password=password, device_type='cisco_nxos')
mac_table = net_connect.send_command("show mac address-table", use_textfsm=True)
mac_data = {'mac': [entry['mac'] for entry in mac_table],
'interface': [entry['ports'] for entry in mac_table],
'vlan': [entry['vlan'] for entry in mac_table]
}
df = pd.DataFrame(mac_data, columns=list(mac_data.keys()))
writer = pd.ExcelWriter('mac_table.xlsx', engine='xlsxwriter')
df.to_excel(writer, 'Sheet1')
writer.save()
Result:
mac | interface | vlan | |
0 | c4b2.dead.beee | Eth1/49 | 100 |
1 | c4b2.dead.beef | sup-eth1(R) | 100 |
Note: I added the vlan as well, as I feel it might be needed, but feel free to change the mac_data dictionary.
Hope it helps.
Stay safe,
Sergiu
06-09-2020 06:40 AM
Hi msdaniluk,
This doesn't provide what we need. This lists the mac addresses for an interface in separate rows. What we are looking for is to write mac addresses against an interface in a single cell.
Thank you.
06-23-2020 06:51 AM
Can you please post the desired output?
06-23-2020 06:52 AM
I think this is pretty awesome!
06-23-2020 07:38 AM
Thanks :-)
09-21-2020 08:59 AM - edited 09-21-2020 09:00 AM
could some one help me with this I am getting a couple errors while trying to run the script
line 8, in <module>
mac_data = {'mac': [entry['mac'] for entry in mac_table],
line 8, in <listcomp>
mac_data = {'mac': [entry['mac'] for entry in mac_table],
thank you very much
ps I am just now trying to learn the language
09-05-2022 05:27 AM
I know this is an old thread but for anyone struggling to get the code provided by Sergiu to work you need to change the entries as follows:
mac_data = {'mac': [entry['destination_address'] for entry in mac_table],
'interface': [entry['destination_port'] for entry in mac_table],
'vlan': [entry['vlan'] for entry in mac_table]
}
The key error is because it is not finding the entry within the text which is output from the switch. Hope this saves someone a couple of hours
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide