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!!