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.

Better Networking Through Excel!?

Better Networking through Excel!?

Part 1 - Enabling Macros and building an IP Spreadsheet

I have everyone wondering what the heck I'm talking about right now, but I've been finding Excel to be very useful in many ways simply by extending it a little bit using Visual Basic functions.  Anything from enhancing spreadsheets, such as spreadsheets for projects or IP address tracking, all the way up to generating configurations for large numbers of routers for a project.

[I should note that this series of articles requires a basic understanding of not only networking, but using Excel. You should definitely already be familiar with subnetting, switch and router configurations as I won't be completely explaining absolutely everything.]

We've recently had a project where Network Engineers were using templates to reconfigure routers to change the type of WAN circuit they were using from MPLS to Direct Internet Access (DIA) circuits with IPSEC/GRE tunnels over them.   Due to the fairly high volume lots of little mistakes were made here and there and as a result each conversion took quite some time and had fairly high complexity.  By generating configuration change templates in Excel, not only were we able to cut down on the errors almost completely, but also reduced the amount of time each conversion took.  We were even able to bring in a contractor to handle the vast majority of the conversions, where more skilled techs only had to jump in where there were problems every few weeks or so.  Big cost savings for the 400+ routers we had to do this for.

So, on the next project I was working on we were converting from Juniper routers at sites for a company we merged with to Cisco routers.  There were only a few variables, but each site was basically the same.   Now, for both projects part of it was standardizing on a IOS version to minimize the differences in different IOS versions.  I stress this because although we were mainly using 15.X IOS's for everything, if a tech tried using a 12.X IOS some of these scripts would fail.

So, the first hurdle I had to overcome as a Network Engineer was how the heck do you program in Excel?   Having studied programming a bit, that part wasn't difficult.  What was hard was actually getting excel to run the macros I had found or created.    I'll cover briefly a couple of the main points of getting Excel to work with macros.

First, go to File->Options->Customize Ribbon and select the "Developer" tab.  This will give you access to macros and such via the "ribbon".    Next, you might have to change the security settings in Excel to allow you to execute macros.   On the "Developer" ribbon bar, go to "Macro Security".  I'll let you choose which one is appropriate for you here, but don't have it set to "Disable all macros without notification" otherwise they won't work at all.   I'm guessing you probably also don't want "Disable all macros except digitally signed macros" since you probably don't have a way to digitally sign your own macros.   The final thing you have to do is make sure any spreadsheets you have that you want macros to work in are saved with the .xlsm file extension, otherwise they still won't work!!  Getting complex these days, huh?

The next step is to load the macros you want to use into the workbook.   Either go to the Developer tab in the ribbon and click on "Visual Basic" or hit ALT-F11, either of which should bring up the "Visual Basic for Applications" screen, which will hide your spreadsheet for now, that's ok.   Double click on the "This Workbook" from the selector on the left-hand side, and your screen should look like this:

Here is where I sometimes get differing results.  I recommend to go to the "Insert" menu and insert a "New Module" and paste these macros into that.   You can then close this window and I would recommend saving this file, remembering to save it as a ".xlsm" or "Macro Enabled Workbook"

DottedIP2Long

LongIP2Dotted

Netmask2CIDR

CIDR2Netmask

InverseNetmask

*Disclaimer* - I did not write the first two macros (LongIP2Dotted, DottedIP2Long), but unfortunately am not sure who to attribute them to!  They work as follows:

DottedIP2Long will take a dotted quad notation (ie: 192.168.0.1) and convert it to a long integer that you can then do math on.   LongIP2Dotted will take a long integer and convert it back into a dotted quad notation for you.

Netmask2CIDR will take a netmask, like 255.255.255.0, and convert it to a CIDR, in this case 24.   CIDR2Netmask does the opposite.  InverseNetmask is used when doing various things on Cisco routers, like EIGRP routing or some ACL's.

You only need to put in the macros that you do need, but it doesn't hurt to have them all in there.  The macros I developed require the first two to work.   There are other dependencies that are detailed in each macros comments.

Now to start having fun, let's build a simple IP spreadsheet with it.   Lets start off with having the columns being "Subnet", "Netmask" and "CIDR".   I'm going to set the background color of any computed cells to light green so I know they are, in this case we're going to compute the "CIDR" column.

As you can see, for C2 I have the value set to "=+Netmask2CIDR(B2)" and I replicated this to C3 and C4.  It is correctly calculating the correct CIDR based off the netmask.   For those of you who are paying attention, you could just as easily have the CIDR be the value you put in B2, and using the CIDR2Netmask function figure out the dotted-quad Netmask!  Do it any way that you want...

Let's fast forward a bit and put in some more potentially useful columns.   "Router IP", which is the first IP in the subnet that is reserved for the router.   "Broadcast Addr", which is the broadcast address of the given subnet.   "Num Usable IP's", which is the number of usable IP's you get for a subnet of that size.   "1st usable IP", which is simply the router IP plus 1 (or if you want to reserve more IP's for HSRP, switches, AP's or whatever you can increase this value!).  "Last usable IP", which is the last usable IP in the subnet.   Depending on your environment you might also want to reserve other IP's and such.   So the spreadsheet will now look like this:

Lets examine each cell one by one.   I might go out of order a bit.

Router IP:  The formula to put in here is "=+LongIP2Dotted(Dotted2LongIP(A2)+1)".   This formula converts the dotted quad IP (ie: 10.8.8.0) to a long integer, adds 1 to it, and then converts it back.  Pretty simple, huh?

Num Usable IP's:   Here is a little trick in subnet math.  The formula is "=+Dotted2LongIP("255.255.255.255") - Dotted2LongIP(B2) - 1".  You might first wonder that B2 is a netmask, why am I using IP routines on it?   Well, it does the math for me.   If you were to take the maximum # of IP's that a dotted quad notation can represent, which would be all "255"'s (plus one), and subtract the numeric representation of a subnet mask, you get the total number of IP's in that subnet.  For the total usable you'd need to subtract one for the subnet IP and one for the broadcast IP.   Rather than add one and subtract two, I choose to just subtract 1 here.   This is pretty much the equivalent of inverting the netmask and converting it to an integer.    Trust me that the math works.   Bonus points to those who remember that there is an "InverseNetmask" macro, you could change this cell to "=Dotted2LongIP(InverseNetmask(B2)) - 1" and get the same result!!  Here you want a numeric result, so no reason to convert it back to anything else.

Broadcast Addr:  This one is using the value from "Num Usable IPs" to compute it.  The formula is "=+LongIP2Dotted(Dotted2LongIP(A2)+F2)+1".  This is simply converting the subnet to a long integer, and adding the number of usable IP's to it to get the last usable IP, adding one more, and then converting it back to DottedQuad.

First Usable IP: "=+LongIP2Dotted(1+Dotted2LongIP(D2))".  Quite simple, take the subnet address, convert to a long, add one and convert back to dotted quad.

Last Usable IP: "=+LongIP2Dotted(Dotted2LongIP(A2)+F2)".  Also simple, take the subnet address, convert to a long, add in the # of usable IP's, convert back to dotted quad.

Kind of neat, but what if you wanted to get a bit more automated.   Maybe have it do a whole bunch of sequential subnets of the same size automatically?   Just delete lines 3 and 4 and instead put in column A1 a formula that takes the "Broadcast Addr" from above and adds one.   But rather than reference F2 (Num usable IP's), lets substitute in the InverseNetmask version of the formula.  So, we'd end up with 

"=LongIP2Dotted(1+ Dotted2LongIP(A2)+Dotted2LongIP(InverseNetmask(B2)))"

Replicate as needed and you end up with something like this, everything computed from the first two values really.   The subnet and netmask, pretty cool, huh?

Here is a copy of this spreadsheet for those that might be having difficulties.

IPSpreadsheet.xlsm

In my next blog post I'll show you how I translated this into using these to configure my Cisco routers when you need to deploy similar configurations to many different sites!!

   

Welcome!

Glad you stopped by.   This blog probably won't be too active.  Planning on sharing things that might be interesting to other folks involved in computer networking.  Guessing most things will be Cisco oriented, but I'm also a Solarwinds fan.  Recently I was named on Thwack.com a Solarwinds MVP, which I consider to be a great honor.

The first post should be up soon, on how to do repetitive networking tasks better using Excel.  Seriously, it does make things simple if you do it right!!