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.