topic Re: Python script to get all mac address against an interface in a single excel cell in Controllers

Python script to get all mac address against an interface in a single excel cell

pfillips11 — Sun, 07 Jun 2020 01:38:31 GMT

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.

Re: Python script to get all mac address against an interface in a single excel cell

ngkin2010 — Sun, 07 Jun 2020 03:13:04 GMT

Hi,

Could you post your current implementation of code?

Re: Python script to get all mac address against an interface in a single excel cell

balaji.bandi — Sun, 07 Jun 2020 07:53:55 GMT

here is the git source you can play with the script how you like your desired output.

https://github.com/routetehpacketz/cisco-ip-trace

Re: Python script to get all mac address against an interface in a single excel cell

Sergiu.Daniluk — Sun, 07 Jun 2020 10:02:20 GMT

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

Re: Python script to get all mac address against an interface in a single excel cell

pfillips@gmail.com — Tue, 09 Jun 2020 13:31:03 GMT

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.

Re: Python script to get all mac address against an interface in a single excel cell

pfillips@gmail.com — Tue, 09 Jun 2020 13:32:13 GMT

Thank you Balaji. I am a newbie at it. And looking at the code I can not find anything which provides what I need.

Re: Python script to get all mac address against an interface in a single excel cell

pfillips@gmail.com — Tue, 09 Jun 2020 13:40:44 GMT

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.

Re: Python script to get all mac address against an interface in a single excel cell

balaji.bandi — Tue, 09 Jun 2020 18:20:13 GMT

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?

Re: Python script to get all mac address against an interface in a single excel cell

ngkin2010 — Wed, 10 Jun 2020 07:27:16 GMT

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.

Re: Python script to get all mac address against an interface in a single excel cell

juan-ruiz — Tue, 23 Jun 2020 13:51:13 GMT

Can you please post the desired output?

Re: Python script to get all mac address against an interface in a single excel cell

juan-ruiz — Tue, 23 Jun 2020 13:52:41 GMT

I think this is pretty awesome!

Re: Python script to get all mac address against an interface in a single excel cell

Sergiu.Daniluk — Tue, 23 Jun 2020 14:38:51 GMT

Thanks 🙂

Re: Python script to get all mac address against an interface in a single excel cell

Steven King — Mon, 21 Sep 2020 16:00:34 GMT

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

Re: Python script to get all mac address against an interface in a sin

TheRivanKing — Mon, 05 Sep 2022 12:27:45 GMT

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 🙂