Network Automation - Nornir Pyxl Plugin - Import Excel data into Nornir!

hey everyone i wanted to go over the new norner pixel plug-in that i've been working on the last couple days uh it's just a simple open pixel plug-in for norner basically one thing that i've come to realize unfortunately us network engineers don't always get our data from our team or co-workers or other team members in other projects you know in pretty json format or whatever so i found myself importing data into my playbooks or run books in this case you know creating custom functions and glasses to wrap either pandas or pi excel and bring some data and work with it so i kind of got tired of that and i started experimenting with this so this is available now on to downloaded via pip so it's just a simple pip install and you should get it let's put a quick example here on how to use it um i i'm not too sure if this should stay this way i think it's still early on i could change it to make something that makes a little bit more sense for those who have not used the pi excel um pip library i'm sorry python library mess this wb is pretty standard for workbook workbook and i figured structured data would be a good uh uh kind of clarification of what you're getting back in this one and in the function so let's just take a quick look at it and see what it's actually doing uh basically it's just wrapping pie excel into norner so you give it a couple different parameters so just a workbook file so you just tell it where the excel file is as well as the name of the sheet name and that's pretty important one thing i do hate about this pixel is it's very specific and not that i hate it it's just that sometimes it bites you if the worksheet has um spaces or whatever else such as you know upper lower cases you know you gotta be you gotta put in exactly what's the worksheet name so just keep an eye out for that however once you do that there's really not much more um it is only read only always so i kind of hard code that in here and it makes it easier to open and it's quicker there's a couple other things if you want to read through this that you can manipulate your data with or or not manipulate but what you get back exactly so basically you just load the workbook with the file name um and there's a couple parameters that you pass in and the read-only will always be set to true and then you go dig into the workbook and open up the worksheet by sheet name um and then basically i'm just gonna append the first row as your keys and then the rest will be your pairs and then you get a nice pretty dictionary for everything in the excel file and then you close it so let's just take a look at a simple example that i put and i'll upload a link to this github repo that i've created um to show in this video i just found this on the internet it's a simple data excel sheet there's some sales orders here and you can see the sheet name is sales orders so let's just uh before we get too deep i'll show you what the structure of the runbook will be using um i've got a couple playbooks here so run books i should say sorry bgp excel deploy which is a i'll show you guys what i how i envision this being used etc etc um just some directory for logs the inventory and then i just created input data and that's where i'm putting my excel sheets and then here's the simple data example run book and we'll just go ahead and take a peek at that real quick so here we go i'm sorry here it is so here's the actual data that we're going to import so there's about 150 okay 150 rows in here um and then we'll take a look at the actual script and it's pretty basic right we're just going to import this function from the new plugin after you pip install it um and then i'm just going to create pass in the task the task into a function and then i'm going to get get the um pass in the where the excel sheet is and then the sheet name is again sales order you gotta mine those capitals in there so then i just loop through it just like any other dictionary in python so then i just print it out so then at the end here just we'll just call it and run it so let's take a look at and see so again there's 150 lines or rows i should say um say python3 [Music] and there it is so that's pretty neat now the one thing that could get tricky here is i'm not doing much processing so i'm kind of letting the input you know have to be formatted correctly and my main formatted correctly is you know always have your columns your top row one columns as your keys that you want to assign um if there are none or empty spaces or sometimes there's like um if someone clicked on a sheet and a cell and like hit a space or i'm not sure why but sometimes i've noticed that you'll get back an empty field and it'll be recognized by by excel so we'll talk talk about that here in a bit but this is kind of the formatting that we should follow um you can start from different rows and different columns but at that point it's just you might as well just write your own code for that because it it could get pretty complex so let's just show exactly what i'm talking about with some errors and some manipulation will have to do in post-processing the one thing that i did do when bringing in the data is i did just do a quick strip the last thing i want is to if there's a space after rep here for example then you know when you're trying to um when you're trying to use your actual dictionary data and you call that key it's just it's yours you don't want to worry about a spacing issue so that's the only thing i could think about that was worth adding into the into the actual plug-in um so let's close out of that now i created um just an example here what i thought could really showcase what you can do so well you know like i said in this field sometimes the data that comes your way and you gotta do stuff with it um you know it's not always you know json or you didn't just download it from you know you didn't call it from an api and it came back to you in a format that you can use it so um you know that's my whole reason for wanting to bring this into the table here so um for my example and some somewhat of a real world scenario that could happen and you wanted to come up with a way to get data from say like a you know some sort of sales person or something like that or you know maybe a team that collaborate on this or someone maybe that's not up to speed on you know maybe even simple as writing a yaml father or something like that you could just create a spreadsheet right with however you wanna input your data into your program so let's take a look at what i've done here customer bgp info so this is the spreadsheet i created it's just very simple you just assign a s number the ip address that's going to be assigned to your interface and sorry i should specify uh the the whole the whole point here is going to be create create bjp neighbors right with just the simple data so you'll give the network which is going to be and the you know the host bit is going to be assigned to the neighbor so 23 out of the 31 and this the site people belong to your neighbor and then you know the cool thing with excel is that you can get pretty fancy so i just made like a quick example here so say what group or policy map in cisco world you want to assign it to you could create little drop downs and then that way you know that your data will always be consistent you don't have to rely on users typing stuff in and then i kind of threw in a curveball here just to kind of show how things can really go wrong so tess wants us to but as you can see there's nothing in column for column d or row one here so if we follow the logic of the program it takes everything in this column and i'm sorry in this row and then it appends it by each cell to uh to the corresponding key so we'll deal with that in a post-processing of your program so i'll just kind of give an example um and then just a quick vlan so we'll just close out of this and we'll just kind of show the topology and then we'll actually run the playbook and run book i'm sorry same playbook and then basically what's going to happen um we'll deploy to the 7750 and pretend this is an isp fabric so if this were to be like a data center somewhere and we've got a couple different peers that we want to appear with all at once maybe you collected the sessions over a couple weeks and it's time to maybe do change management and deploy some new configurations so now that we've got all our data we will look at the program just close out of this and here is the actual run book of what i created so i'm going to bring some stuff in we're going to use netmego right file template file standard plugins we're also going to take advantage of the ipv4 network from the ip address library um one one custom function here so it's a create folder just so we can kind of organize where data is going so the first one here is render bgp neighbors so here's when we call in the workbook structure data we'll just assign it to a variable named data workbook is the input data folder and spreadsheet and bgp data is what i call the excel sheet i'll just make sure that the output folder is created in my directory here then we just look through it right so let me close let me not actually deploy it and we'll just we'll print this out so we can see the errors that we get and then we'll just so as you can see here it brings in the none and test and then none test and then the third one i actually left the cell empty so it's not none so i didn't think it was going to be too general then it was gonna have to be too much it was gonna break it if i try to process this beforehand so i left it so there's there's really what should happen is the data should be formatted correctly when coming in and then if you need to do some processing post-processing there's some pretty simple ways to do it so let's just close the uh i'm sorry we'll just kill this line here 36 and then we'll actually assign a new line to appear oops all right so we'll just do some list comprehensions and this is kind of this one took me a minute to think about um but really it's just key for value pairs and then uh look through the items of it and basically just gonna filter right we're just gonna filter through the dictionary if it's none then just drop it um once that's done then we're just gonna get a little fancy here we're gonna call in the ip object out of the ipv4 network and then we're going to assign that to the ip address one thing to note here for those of you who are familiar with the ip address library strict false should be enabled if you're working with the slash 31 um otherwise you're gonna get an error i don't recall what it is but something to keep in mind um and then we just created uh we list through the host of this network and um sorry i should have written this notes here so then we're just going to create a couple couple different two different keys so the local ips so that when we render our template i can make sure i have my correct side of the peering relationships ip so we'll just call that host 0 which is the first element in the list and then we'll append back the ip prefix length which should match whatever we already stripped out of it by well it's been assigned to the ip4 network as an ip object so it's there so we just pull it out of here and append it to the first element of the list like i said and then that's what's going to be our local ip and then just kind of the same thing for peer except for when you're creating a peer pgp neighbor you don't really need the prefix so we'll just call the second element which will be the next hop or just say your your neighbor and then we'll just create that as a neighbor ip um and then i just had a print same here just so we can see it switches showing here so actually let's look at it again so now those none values are eliminated um however i did have an exception uh being raised here and i just threw it in there so that if something can't be if something's none it can't be stripped and so you'll just see that come up and maybe you can go back and look at your data and see what's going on in any case that's there so now we've got this neat structure data which is you know the the key here asn ip address the group the vlan that we're going to use which is in the data center fabric um so let's go look back here um our connection from our routers is going into the switch and this basically is just one connection this you know this could be a pretty beefy connection 10 gig or something 100 gig and then just gonna assign the vlan and then these are you know the big big wolves in the internet so let's take a look at the next part which would be now that we've got that data we're just going to write a template and the the commands that we're going to send to our device and that should be generated into the output folder here so you can see that that's already happened because i just ran it um and then i'm gonna move on to the next bar and actually deploy it so one thing that i've been struggling with or fighting with in my own personal life is uh i i run linux as my main os and my work computer is a windows machine unfortunately so little things like this they're very important you know that's there's a lot of ways that you can make sure that everything will work with in your linux environment as well as in your windows environment such as paths is really one of the main issues that i always run into so that's what this is um basically we're just gonna open the file and just gonna split the lines up and strip them and get rid of some stuff that we don't need in there um and then we're just gonna send all those as a config set or send us a config which is the norner that miko plug-in function and so once that's done then we're just going to go into that folder and use the sheetil move utility and just move these files in into this other folder output backups and uh there's some stuff in here from before but it just overwrites it if it's already in there um so just delete that just so you can see it happen and just run it again um and once that's done then we'll just get the show router interface which is the equivalent of a show ip interface brief or show ip or whatever and cisco let's get that to the screen and then once that's done we should actually see our sessions come up except for one minor thing that i need to do [Music] on the i just rebooted it to lose the config see that there's nothing here no no configuration at all so let's just run it and see how we can take advantage of our new plugin and by using structured data from excel sheets so i left that print statement in there just so we can see it again and you can see that this folder got created now we've got no it's already done so these new interfaces got created um with the correct vlan id on the correct interface now let's take a look at our bgp neighbors and you can see that they're already established 15 seconds ago so that's basically it um i just thought it'd be a quick example of um just kind of how i envision this being used or you know simple simple ways to bring data in and you can do some fun stuff with it so thanks for watching

Channel: Admin Save

Views: 287

Rating: 5 out of 5

Keywords: network automation, nornir, ansible, excel, python3, cisco, nokia, devnet, pyxl, nornir3

Id: itAS9j7vtBQ

Length: 20min 6sec (1206 seconds)

Published: Sun Nov 29 2020