Automation: Making Better networks

Posted by joekukis

As part of the same project I wrote this python script for, I created an Excel/VBA script to allow our team to quickly and consistently input all of the data required for the VPN hardware we were shipping out to over 450 locations. The output of this Excel spreadsheet would later serve as the input to the Python script I wrote, and combined they are working out very well.

Why Bother?

Before I dive into the Excel/VBA code I’d like to give a little bit of background on my thoughts on why this was worth getting into. From my experience, it’s fairly easy to come up with the configuration for a single site, or even a couple of sites. You have the time to verify everything is correct, put everything in by hand, and really dedicate the time to check everything is how you want it. This gets more difficult as you scale in size. Even at 10 or 20 sites you start to increase your margin of error for a typo here or there, or you might run out of time and not be able to check everything as well as you would like. Once you start to get into hundreds of devices to configure it makes things that much more complex. It’s now very difficult, if not impossible, for a single person to manually configure each location and requires a very large amount of time dedicated to a single project. If you take the time to automate a process, whether it is with a script, Excel, or some other combination of tools you can reduce the number of human errors as well as reduce the time and resources that would otherwise need to be dedicated to the project.

The problem

The project that led to this particular Excel VBA script required shipping out VPN hardware to over 450 locations. Each VPN appliance was shipped to us by the carrier and was already assigned to a specific site. There were a few different unique pieces of information, all in different spreadsheets that all needed to be tied together:

Spreadsheet including Site ID and MAC address of VPN appliance
Spreadsheet including Public IP address information from various broadband providers
Spreadsheet including Internal IP addresses and identifying the type of configuration each site would get

The different types of configurations for each site were important as they dictated what equipment and information would need to be set up for each site. The three options were:

Broadband as a primary connection with a cellular USB as the backup
Combination of broadband and T1
T1 with cellular backup

If you had a site that fell under ‘option 1’ then it required entering in the public IP address information for that site, as well as keeping track of the cellular SIM ICCID and IMEI numbers. If you had a site that was under ‘option 2’ you would only enter in the public broadband IP address, but would not need to package any cellular USB sticks. If you had a site with ‘option 3’ then you would not have any broadband IP information to enter, but would need to package a cellular USB stick and record that information down.

Doing any of the above manually would be very labor intensive, flipping between multiple spreadsheets to check the type of setup the site would have, figure out which information to record and ship out. So, we automate.

VBA

Prior to this project I hadn’t written a VBA script since middle school. I ended up having to re-learn a number of things to write this script but in the end it was worth it. It has a lot of similarity to a pivot table, with some added extras. The idea behind the script is this:

Every VPN hardware appliance has a barcode on the box that includes the MAC address of the device. We can use a barcode scanner to scan that box and do a lookup of the MAC address in Spreadsheet #1 which will give us the site ID we are working with.
Then do another lookup of that site ID in another spreadsheet and pull the type of configuration(Broadband,Cellular,T1 combinations)
Prompt the user for the appropriate required information, depending on which type of configuration the site will get. For example, if the site will need Broadband and cellular, then display the fields for broadband IP address and cellular information. If the site will be getting a T1 with cell backup, don’t prompt the user for any broadband information.
At the end of the script, tell the user which instructions to package with the device before it gets shipped out.

Some screenshots

Here’s some screenshots of what the tool looks like when run with a Broadband/Cell Site:

1) Start off by scanning the barcode of the VPN Appliance

2)Prompt the user for the appropriate information so it can be saved to a database.

Detected this was a broadband/cellular site. Prompt the user for the appropriate information.

3)Present the user with all of the necessary information for that store so they can enter it into the VPN gateway.

Present the Broadband IP address information to the user for this specific site so they can enter it into the VPN hardware appliance.

Excel IP Functions

Posted by joekukis

I was working on a project the other day where I had a list of ~ 500 /30 subnets in Excel that I needed to break out so each host in the /30 was in it’s own column. Seemed pretty straight forward, but there was no built in function to handle this. With all of the other math functions Excel has built in, you would think that in 2014 some functions that deal with IP addresses would be standard. Guess not. Rather than spend the time to write one from scratch, a quick Google search came up with an entire set of functions written by Rajeev Bhardwaj. He put together an excellent presentation that shows the usage of each of the functions located here: http://www.slideshare.net/rajivss/ip-functions-presentationgen-v11 . This not only helped me solve my current problem but all of the other tools included in the Add-In will definitely get used in the future.

Here’s a quick summary of the included tools:

IP_Bits2Mask – Converts the number of bits from a / notation to an expanded subnet mask
IP_ErrChk – Checks to see if a value is a valid IP address or not
IP_Hosts – Calculates the number of hosts from a specific subnet mask
IP_IP2Mbits – Calculates the subnet mask required to obtain a certain number of host addresses
IP_Mod – Takes any octet of the IP address and increments/decrements it by the set value
IP_NextSub – Calculates the next subnet
IP_Bcast – Calculates the broadcast address for a given IP
IP_Count – Counts the occurrence of an IP in a range of subnets
IP_IsExist – Check if an IP address exists in a subnet
IP_Mask2Bits – Calculates the mask bits from a subnet mask in dotted notation
IP_Subnet – Calculates the subnet address for a given host IP

I would recommend you visit Rajeev’s site and download the Excel add-in to be able to take advantage of all these functions. You can visit his site to download the tools here: http://rajivbhardwaj.com/download/

Work smarter, not harder : Excel Concatenate

Posted by joekukis

As a network engineer there’s often times when you need to deal with repetitive tasks such as creating configurations for network devices at multiple sites. Usually these configs are based on some template, with only minor differences such as IP addresses. If there are only one or two sites it isn’t bad to create these configurations manually, but if its 10 sites, 20 sites or, hundreds of sites it becomes much worse. While there a number of different tools or scripts you can program to create these configs, one of the simplest that almost everyone has is Microsoft Excel.

The Functions

Concatenate()

The concatenate function in Excel allows you to take a number of different cells and/or strings of text and concatenate them together. For example:

=Concatenate(“This is “,”an example”)

Will generate the text “This is an example”. Not the best way to use the function. A better way would be to refer to cells in the function.

For example, this function:

produces this output, pulling the values from cells ‘A1’ and ‘B1’ and combining them with text

Char()

The Char() function in Excel returns a character based on the Ascii value. You can pass any ASCII value to the function and it will output the corresponding character. The main one we are interested in is the “New Line” character. When we use this in combination with Concatenate() we can put text on different lines. We can generate this by using ‘CHAR(10)’.

Here’s an example:

will produce this output:

Note: In order for Excel to display the text on different lines you would need to highlight the cell and click the button.

Building Network Configlets

Now that you understand the basic function we can apply it to networking. Let’s say you have a spreadsheet listing the various office subnets in 10 different states. The network was designed so each subnet is a /27 in size with a mask of 255.255.255.224.

Each site will need it’s own BGP configuration with a unique AS number and advertising all of the subnets at that site. One way to do this would be to do it manually. Another would be to use the concatenate function in Excel. Let’s say the config you want to create looks like this:

router bgp <as>

network <wired_network> mask 255.255.255.224

network <wireless_network> mask 255.255.255.224

network <voip_network> mask 255.255.255.224

Using the concatenate() and char() functions we can generate a config per site in only a couple of minutes. This function: generates this configlet:

The funtion can now be copied down to the other rows to quickly generate the rest of the sites BGP config:

Exporting the Configlets

The last step in the process is to get the configlets from Excel into a text editor or straight into the Cisco CLI. If you try and copy and paste the cell you used the concatenate function in you will wind up with all of the text between two double quotes. Since the cell contains the function itself, you can’t double click the cell and copy the text itself. There are two ways I’ve found to deal with this.

Option 1

One option is to click on the cells you want to copy and paste them into a text editor like Notepad++. This will give you each configlet surrounded by double quotes. You can then do something simple like find/replace the double quote with nothing. See below for a Notepad++ example of this:

Option 2

For option 2 you can select the entire column containing the concatenate function outputs and paste them into another column, using the “Paste Special – values only” option. Once pasted, this will allow you to select and copy / paste the text within the cell(not the cell itself) directly into the CLI without quotes.

Conclusion

This was one example of how to use Excel to make building multiple configs easier. It helps to reduce human error as well as decrease the amount of time spent manually building configs. Other use cases I’ve found for this are building Access List Entries. It may take some time to get the fields you want to work with in Excel, but once they are in there it becomes a very powerful tool.