Preparing to build a Cisco config with Excel

Better Networking through Excel!?

Part 2 - Preparing to build a Cisco config with Excel

Ok, so the scenario is this.    Your company is acquiring 100+ sites that you and your colleagues are going to have to bring online over a period of 3-4 months.   Up until now your company has been using mainly an MPLS network, but they want to use this to test the possibility of using internet links (DIA - direct internet access) to bring these sites in.   For now they just want to bring them all in to a head-end router that you currently have, but will probably at some later point want to purchase new routers and migrate them to a DMVPN solution.   But for now they want quick and dirty encrypted GRE tunnels brought up to all of these sites that all currently have Cisco 1841 routers at them.

So, the idea is to build 1841 configurations that can be loaded on the remote routers by someone in the field.   At the same time, to build the head-end configuration for your head-end routers.   You've tested it on a couple sites, but now want to start deploying to all the rest of the routers as you get the essential information like WAN circuit info and such.   All of these configurations are essentially going to be the same, so you decide to take your template from one of the first couple routers you deployed and make a configuration builder.  Let's do it with Excel!!

The first thing I like to do is load the template configuration in to a sheet in Excel.  So, simply cut and paste the config into a separate sheet called "Remote" in your Excel spreadsheet.   I will then go through the sheet and change the background cell color for all the lines that will need to change with each config.  Like this:

Figure 1 - Remote Sheet

When doing a complete config, most of your lines will not change, but if instead you were only doing a partial config, most of the lines might change.   We're just going to leave the lines for now, we'll go back and make them dynamic after we've done a bit of magic.

The next thing I will do is basically the same thing, but with the head-end configuration instead.  If you have a good configuration management tool, you can use it to find out what lines in the configuration changed from before you added one of your testing sites to after.   Whatever way you do this is fine, but you'll end up with another separate sheet called "HeadEnd" in your spreadsheet.  Go through and highlight the items that will change in here too.

One thing to note is that Cisco makes this type of scripting easy on their routers because you can simply add to existing crypto-maps, access-lists and things like the EIGRP configuration.  If you were to have to re-enter the entire EIGRP configuration each time you wanted to modify it, this would be very difficult to do since you don't necessarily know what's already in the configuration.  Some vendors like to number things too, like if you were adding another static route you would have to know what number route is available to use.   Things to keep in mind if you are trying to do this with other vendors configurations, and be thankful if you're working with Cisco or a similar vendor!!

The next sheet I will make is one called sites, which is simply a list of some or all of the sites we're doing, one per row, with all the relevant info in one simple list.   The contents of this list might vary based on the particulars of your installation.   In this case we have the Site Name, off of which we decide on a "Hostname" for the router.   We then assign each router a Loopback address, a LAN Subnet and subnet mask, a Tunnel subnet for the GRE tunnel and a Tunnel # for the Headend router.

There is also WAN circuit info here with things like the subnet that the WAN circuit will be on, the circuit type, vendor, vendor phone #, circuit ID and if applicable - a DSL username and password for those sites that DSL was the only option.  The sheet should look something like the picture below.  I highlighted the columns that we would generate the info in blue.   The tunnel # is simply sequential, but the Tunnel Subnet column is generated off the first value I assigned. 

Figure 2 - Sites sheet

So we reserved the 192.168.0.0 subnet for Tunnel Subnets.   We decided to do them as /31's to fit as many in as possible so it only uses 2 IP's total.  There is no "Subnet" or "Broadcast" address on this type of subnet that you can't use, only 2 usable IP's.   This is explained in RFC 3021 or you can find more info on one of my favorite blogs at packetlife.net in their article "Using 31 bit subnets on point-point links".   Excellent resource!!   How, by putting in the first subnet, did all the other subnets get generated then?   Simply using what we have built on before!!   In cell F3 I put in the following:

   =+LongIP2Dotted(Dotted2LongIP(F2)+2)

and then simply replicated the formula as far down as I needed.  This took the starting subnets IP address in cell F2, converted it to a numeric value using Dotted2LongIP(), added 2, then converted it back again to dotted-quad notation using LongIP2Dotted().

The next step I do is to create a "Variables" sheet that has all the values that I need to generate my configuration.   Now, you might be saying that everything I need is on the "Sites" sheet, which yes, most of it is.   But I don't always create a sites sheet, only when I have a lot of sites or need to work on the fly a bit.   Sometimes I just have this "Variables" page and fill in the blanks directly to it.  I use a feature called "Named Cells" quite extensively in Excel from this sheet in order to make setting up the formulas on the other sheets easier.   You can find more information on that from this article at office.com - "Define and use names in formulas"  However, since I do already have a lot of information on the "Sites" sheet, I'll leverage that information by using the "INDIRECT function", which you can find more information on once again at office.com by following that link!

You'll see evidence of me using the INDIRECT() function at the very top of the variables sheet where I ask for the "Site Row".  This would correspond to the row of the site on the Sites sheet of the site you want to generate a config for.   So, for Austin it would be "2", and for "Las Vegas, NM" it would be "6".   This would be in cells A1 and B1 as shown below.

Figure 3 - Variables Sheet

I like to divide my variables into groups, the first group I made is the "General Info" group.   The first variable I have in this group is the hostname, which is in column B on the "Sites" sheet.  So, I use the formula

     =+INDIRECT("Sites!B"&$B$1)

in order to get this value in here.   How does this work?   The INDIRECT() function first evaluates its arguments and then uses those as a reference to the cell where the value is really stored.  The "&" character tells it to concatenate what is before and after it into one string.   Quoted text stays as-is.  So, we have two parts here the "Sites!B", which stays just like that (without the quotes) and the $B$1 (or B1), which is the "Site Row" cell that we mentioned before, currently set to "2".  So, add these two together and you get "Sites!B2" which references the B2 cell on the Sites sheet.   Pretty nifty, huh?

Before we go on, lets name this cell "Hostname" so I can easily draw it up on any other sheet in this workbook.   Right click on the cell that should now have "Austin" in it (cell B3 on the Variables Sheet) and click on "Define name...".  In the "Name:" box, just type in "Hostname" and click on "OK", as shown below:

Figure 4 - New Name requester

Now you can refer to cell B3 on the "Variables" sheet to always come back with the current value of that cell.   And, by having the value in that cell indirectly reference the row you want on the Sites sheet, it makes it even easier!

One point to always remember, if you want to edit or delete any Named Cells you might have in your sheet, or if you just can't remember their names, you can go to the "Formulas" tab and click on "Name Manager" and it will bring up a listing of all the variables you have defined, which will look something like this:

Figure 5 - Name Manager

You can see not only the name you defined, but the value it currently holds.  So, look at the "Hostname" line in the first column and follow it over and you'll see "Austin".  It then tells you what cell (or range) it refers to and what the "Scope" is, which by default is the entire Workbook that you're working in.   The output above shows us all the Named Cells we will be defining.  You will note I try and use a consistent naming scheme.  This is a preview of all the Named Cells we will be defining.  You will note I try and be consistent in my naming scheme, which makes things a bit easier.  So all "Subnets" I end up defining the Named Cell starts with "Subnet", all the Netmasks I define start with "Mask", etc. etc..

Lets get back to the variables sheet in Figure 3.  The next value is the "Crypto Key", which must match in the crypto policies on both ends of the IPSEC tunnel.  You can figure out how you want yours set up, in this case I used a random string of characters followed by the Hostname, then a "-", then the GRE tunnel (which I have yet to define).  So the formula ends up being:

     =+CONCATENATE("!aABXZ$$sjRP", Hostname, "-", GRETunnelNum) 

Now, if you define this before you do the GRE tunnel number, which will be cell B18, you should get an error in the cell that looks like "#NAME?".  That's ok for now, its just telling you that there is no such named cell called "GRETunnelNum" yet.

The next couple cells are simple INDIRECT() references to the Loopback Address in column C and the LAN Subnet address in column D of the Sites sheet.   Here is the "Loopback Address" formula:

     =+INDIRECT("Sites!C"&$B$1)

Since we decided to use CIDR (Classless Inter-Domain Routing) notation, otherwise known as "slash notation" for the subnet in the Sites sheet, we need to convert it for the next value, which luckily we have a macro for that called "CIDR2Netmask()", so the formula is:

     =+CIDR2Netmask(INDIRECT("Sites!E"&$B$1))

Now, the next two subnets and their corresponding masks are kind of interesting.  We never had any definition for what the subnet would be for either the "Wireless" subnet or the "Guest" subnet, so how do we get them?   You'll note in the Sites spreadsheet all of the networks in the LAN subnet were at a minimum 8 away from each-other in the third octet.  The first octet is in all cases "10.", which are all private IP addresses.   The second octet is assigned arbitrarily based on the state they are in, so you'll notice that "100" is TX, while "0" is NM, "50" is AZ and "63" is NV.  So, Austin and Houston were assigned consecutively and both are in "TX", which would mean their subnets would start with "10.100".   Austin is assigned "0", while Houston is assigned "8", we did this to provide plenty of room for growth or other uses at each site.  The LAN mask is in these cases either a /23 or /22, which are 512 and 1024 IP's respectively.   The wireless subnet starts immediately after the LAN subnet, and always has a /24 of IP addresses.  The guest subnet starts immediately after the wireless subnet, and always has a /24 of IP addresses.   None of this was on the Sites sheet, so how do we handle it?  Simple math is how!!

Lets do the "Wireless" subnet first, which will be cell B10 on our Variables sheet.  Since the LAN subnet can be a few different sizes, we need to be able to figure out where this subnet will start.   What do we know so far, we know the subnet and netmask of the LAN, which is all we really need to know in this case!   A useful-to-know math fact about IP addresses is that if you take the inverse of a netmask (ie: convert it to binary and change all 1's to 0's and 0's to 1's), add one, then convert it to a decimal, you get the size of the subnet.   If you add this number to the last subnet and convert it back to dotted-quad notation, you get the start of the next subnet.  Luckily we have a function that gives us the inverse netmask, InverseNetmask() which makes it easy.   So you get the following:

     =+LongIP2Dotted(Dotted2LongIP(B8) + Dotted2LongIP(InverseNetmask(B9))+1)

B8 is the LAN Subnet address, you convert this to a number, then take the inverse netmask of the LAN Netmask in B8, add one and convert it all back to dotted-quad notation and you get the start of the next subnet.   The next line, its subnet, is always a /24 (ie: 255.255.255.0), so we're good there.

The Guest Wireless Subnet is even easier.  Simply replicate the formula above, and paste it in cell B12 and you've got it!  The formula ends up being the same as above, but with B10 and B11 instead, which are the cells of the Wireless Subnet values.

     =+LongIP2Dotted(Dotted2LongIP(B10) + Dotted2LongIP(InverseNetmask(B11))+1)

We just let Excel automatically change those values for us, sweet!  (Or, as a Firefly fan, should I say "Shiny!" :-) )

Once again, the next few values are pretty simple INDIRECT() lookup's until you get to the "DSL" section.   This part is a little tricky.  If it is a DSL line, rather than configuring up a static IP on the WAN interface, we instead have to configure it up as a Dialer interface attached to the WAN interface.    This is partly because these DSL lines require PPP authentiation.  Sounds complex huh?   It really isn't, but I included it here to show you how to handle exceptions.

So, the first value I have in the DSL section is "DSL?", which is simply a true/false representation of whether this is a DSL connection.  True in this case being equal to 1, and false being equal to 0.  I do this so I can use it in "IF/THEN" statements later on as a true/false value that I don't have to convert or compare.   So, to find out if its DSL, you check the value of column I in the Sites sheet and see if its "DSL".  We do this using an IF/THEN in Excel and the "EXACT" function that compares two strings.  If the corresponding cell is "DSL" in the "Sites" sheet, we set this to "1", otherwise "0".

     =+IF(EXACT(+INDIRECT("Sites!I"&$B$1), "DSL"), 1, 0)

The next couple values are basically INDIRECT() lookups, but only happen if cell B25 evaluates to TRUE (or 1).  Otherwise it doesn't bother doing the INDIRECT() lookup and just makes it a blank cell.

     =+IF(DSL, +INDIRECT("Sites!J"&$B$1), "")

Now, if you haven't already gone and set the "Named Cells" as shown in "Figure 5", we need to go do that.   This will set us up for doing the rest of our config in the next blog entry!

Stay tuned!!    This spreadsheet WILL be published in the next blog entry, but for now we'll keep building it.