cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2146
Views
5
Helpful
12
Replies
Highlighted
Beginner

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

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.

12 REPLIES 12
Highlighted
Enthusiast

Hi,

Could you post your current implementation of code?
Highlighted

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.

Highlighted

 

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.

 

Highlighted
VIP Mentor

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 ***

Highlighted

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

Highlighted

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 ***

Highlighted
VIP Collaborator

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

 

Highlighted

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.

Highlighted

Can you please post the desired output? 

Highlighted

I think this is pretty awesome!

Highlighted

Thanks :-)

Highlighted
Beginner

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 

 

This widget could not be displayed.