Part 3 of 3 - Putting it all together - the final config

Sorry for the delay in posting this!   Life happens, huh?

Ok, so back to where we were.   We now have several worksheets set up:

1. Sites - which holds all the sites we're going to set up, 1 per row
2. Variables - which determines the variables we're going to use based on the Sites sheet and names them so we can easily access them from other sheets
3. Remote - which has the remote router configuration template
4. HeadEnd - which has the head-end VPN router template for adding a new sit

On the "Remote" and "Headend" sheet's I've said to go through and highlight every line that will change in green, and that is already done.   Now its just a matter of setting up the formulas to show what's changed!

So, lets begin, the first line in our sheet that changes is row 8, or the hostname for the site.  This one is quite simple, we just concatenate the word "hostname " and the variable Hostname from our variables sheet.  We end up with a configuration line as shown below.

Very easy, right?   The rest isn't that much more difficult.  

The next line is to set the crypto key in the crypto map.   We have already defined our "CryptoKey" in our variables page, so once again its just a bit of concatenation of existing variables and config lines.  In this case the formula is:

=+CONCATENATE("crypto isakmp key ", CryptoKey, " address 5.2.3.4")

And for the Austin site, this equates out to:

crypto isakmp key !aABXZ$$sjRPAustin-1000 address 5.2.3.4

Let's move on to something a bit more difficult.   We have the subnet for the tunnel interface in our variables, in this case 192.168.0.0/30, which is stored in the variable "SubnetGRETunnel".  We want to configure the IP address for the Remote router, which is the second IP in the subnet range, with the headend routers tunnel interface being the first IP in the subnet.  The first thing I'll do is convert the IP address into a value that we can do math with, because its currently in a "dotted quad" notation.  We can do this using the Dotted2LongIP() formula, like this:

   Dotted2LongIP(SubnetGRETunnel)

Once we have it converted to a long integer, we can then do our match on it and add 2 since its the second IP in the subnet, like this:

   2+Dotted2LongIP(SubnetGRETunnel)

 at which point we use the LongIP2Dotted() function to convert it back to a dotted quad notation.

   LongIP2Dotted(2+Dotted2LongIP(SubnetGRETunnel))

Very cool!!   Put this together with a "concatenate" of the rest of the line and we get:

=+CONCATENATE(" ip address ",LongIP2Dotted(2+Dotted2LongIP(SubnetGRETunnel)), " ", MaskGRETunnel)

Which, given the variables for the Austin site, ends up being:

 ip address 192.168.0.2 255.255.255.252

after all is said and done!!   You will see similar formulas for the VLAN interfaces on the routers, as well as on the tunnel interface for the headend router.

Also in the tunnel interface, the other line that might change is the source interface of the tunnel.   Usually it will just be our WAN backup interface (Fa0/1), but if its a DSL connection it will be Dialer1.

What a great way to breach the discussion about the DSL and Dialer interface.  If its cable connection, all the configuration is done on the FastEthernet0/1 interface, but if its DSL we need both the Fa0/1 AND the Dialer interface configured.   Most of the rest of the substitutions are fairly simple, but the way I structured it to do the Dialer interface is quite interesting.   If you have a Dialer interface, the config will look something like this:

While without the Dialer interface it will look more like this.   Note the space it takes up is the same, and the IP address assigned to the interface is always on line 126, but one has a Dialer interface and one doesn't!

That's because when there isn't a Dialer interface being configured, it removes the lines that don't need to be there.  It simply substitutes white-space for those lines.   Let's take line 125 for instance

=+IF(DSL, "interface Dialer1", "!")

If the variable DSL that we defined on the variables page is "true" or equal to 1, the line becomes "interface Dialer1", otherwise its just a comment maker, which is the exclamation point.  (ie: "!").  This is done with all the relevant lines here, be sure to check out their behavior!!

The next interesting section is the EIGRP routing, but its actually quite simple.  I just create a line for each network we have here in the "network" section, and by using our InverseNetmask function, we're able to very easily do this.   Then its just the matter of plugging in each variable and your ready to go!

=CONCATENATE(" network ", SubnetLAN, " ", InverseNetmask(MaskLAN))

Very simple, huh?

The "HeadEnd" spreadsheet is for the head-end router of the VPN tunnel.   It should be noted that its just a delta-config, or it only shows what changes are going to be made to the config that is already there.   As a result, the outbound interface that the tunnel maps to (Gi0/2) is referenced, but you don't see its config.   It also references the transform-set and a few other things that aren't shown in this config either.   But, all of those can easily be extrapolated based on the config of the remote site.

Complete Spreadsheet

Above is the link to the completed spreadsheet, macros and all.   If you have any questions, or corrections to make, let me know!