cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
11041
Views
10
Helpful
13
Replies

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

pfillips11
Level 1
Level 1

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.

13 Replies 13

ngkin2010
Level 7
Level 7
Hi,

Could you post your current implementation of code?

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.

 

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.

 

balaji.bandi
Hall of Fame
Hall of Fame

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

 

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

BB

***** Rate All Helpful Responses *****

How to Ask The Cisco Community for Help

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

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?

 

BB

***** Rate All Helpful Responses *****

How to Ask The Cisco Community for Help

Sergiu.Daniluk
VIP Alumni
VIP Alumni

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: 

 macinterfacevlan
0c4b2.dead.beeeEth1/49100
1c4b2.dead.beefsup-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

 

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.

Can you please post the desired output? 

I think this is pretty awesome!

Thanks :-)

Steven King
Level 1
Level 1

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 

 

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: