Network Automation with Excel using Python
After working in the industry for almost a decade, I can say this with confidence that most Network Engineers maintain and exchange information in excel format. In other words, every Network plan is prepared and after implementation documented in excel format. Although there are many great Network Automation platforms readily available, the common problem with all of them is that in order to use them, original information has to be first manually converted into an intermediate format such as text file or yaml before pushing the configurations on Network Devices which introduces unnecessary complexities. Wouldn’t it be great if we could avoid this redundant step? Well, this is exactly what I will try to do here. In this article, I will show you how you can write Network configurations on excel and push them directly without performing any intermediate step with the help of Python
Before jumping into the code section, let me give you a walk-through of users experience. For the purpose of demonstration, I will use simple topology given below
In the above topology, e2/0 interface of each router is connected to mgmt router. The Management IP of each router should be reachable from the machine on which Python is installed. Initial configurations are given below
Management IPs are made reachable from Python Machine through manual configurations
Initially, only management IPs and reverse route for Python Machine is configured on each router. let me show you the initial config on one of the routers for clarity
Now that you have a good idea of the initial network setup. Let’s take a look at basic vrf, p2p IP, and static routes configurations we will be pushing on Network Devices. Configurations in excel will be as follows
The goal is to generate a text config file for each router and store them in a folder then push them on the respective devices. Let’s see step by step how this can be achieved
Step by step walk-through of Python Code
Read the excel config file, and specify the sheet name as ‘P2P’ to read data as Pandas DataFrame. Next, split the resultant DataFrame into two smaller DataFrames, by specifying the columns each should contain. Now, convert the two Dataframes into standard Python data type, List of Dictionaries. Each Dictionary, in the List, contains one P2P configuration for a specific Router. After following these steps you will end up with List of Dictionaries for Node A (df_listA) and Node B (df_listB)
Now we need to do the same thing to read vrf and routing configurations as DataFrames, simply put the value of sheet_name equal to ‘vrf’ and ‘routing’ this time and then convert the result into List of Dictionaries
Next, we need to capture all unique router names for which configurations are defined in excel since the goal is to generate a separate configuration for each device. The Set data type is best suited for this problem. We can loop through each List of Dictionaries (df_list_vrf, df_routing, and df_list_routing) and store a separate Set for configuration type. Following is the code for storing router names in a Set called NodeSetFinal
We can merge NodeA and NodeB P2P config by replacing the keys with generic keys listed in the ‘columns’ list. It makes sense because configurations are supposed to be stored for a unique router name. It doesn't matter whether the config is a part of NodeA or NodeB columns
Up till this point, we have vrf, P2P IP, and Routing configuration stored in the form of List of Dictionaries. Next, we will create a separate Dictionary in which the key will be the router name and the value, parameters that can be used in the later stage to construct the actual config. We can do that by following these steps
Now we can easily access the configuration of each router by simply putting the key as a router name. Even though the parameters required to construct configurations are almost common, actual config varies from platform to platform. For example, ‘ip route 1.1.1.1 255.255.255.255 10.0.0.1’ is a valid command in Cisco IOS to add static route but in Juniper same thing can be done by executing ‘set routing-options static route 1.1.1.1/32 next-hop 10.0.0.1’. The key parameters are similar in both cases (destination subnet, mask, next-hop IP). So, In order to store and push actual configuration on devices, we need to create Jinja2 configuration templates based on the vendor of Network Devices. In our example, we are using on Cisco IOS-based routers. In the template shown below, terms enclosed within double brackets are placeholders that will be replaced with parameters passed. For example, if ‘vrf’ is equal to ‘red’ than ‘ip vrf {{vrf}} will become ‘ip vrf red’
After preparing all templates and saving them as .j2 files. we will pass parameters stored earlier for each router previously to construct actual configuration
Finally, we will loop over the Set we defined earlier (NodeSetFinal) and merge them to get a complete configuration for each router and then store results in a specified folder called Candidate Configurations. We need to be careful about the sequence of configurations when preparing the final product because it won’t work in a desirable manner otherwise. For example, you cannot add static route in a vrf if the vrf is not defined first. Therefore, vrf needs to be defined first prior to using it. In our example, the sequence will be vrf → P2P IP → Static Routing
Finally, Executing the above code will give us the following output
Now that we managed to construct the required configurations, we can write them on respective devices with the help of a platform called Nornir. First, we will create an inventory of devices in which the Management IP of each device will be called. A simple program will SSH into each device through Management IP and then push the configurations. I won’t be discussing the nitty-gritty of the platform here. There is a lot of material available on the internet if you are interested to learn more. Here, I will show you a simple code that will fulfill our requirements
Hope you learned something new today. If you are interested in playing with the code, here is the link https://github.com/sajidbkhan16/Excel_Nornir. Cheers!