Monday, August 11, 2014

Network Engineer Should Know A Little Bit Scripting and Excel

I was working on a network migration project for a large enterprise.  They are migrating their Catalyst 6509 network to Nexus (7ks, 5ks, 2ks).

Part of the migration is to move hundreds (if not thousands) of servers from 6509 switches to Nexus 2Ks.

In an ideal world, it would be as easy as copy the interface configuration from 6509 and paste it into N5K (where N2K homed to).  But we don't live in an ideal world.

The challenge we are facing are:

1) There are many local significant VLANs due to poor network design, which means, VLAN 100 on legacy switch may or may not be the same VLAN 100 on new switch.  Thus you cannot just blindly copy the "switchport access vlan 100" command from legacy switch and paste it into new switch.  We might have to create a L2 trunk from legacy switch to new switch.  We might have to create new VLANs and SVIs.

2) Even if the VLANs are perfectly fine, copy/paste the configuration for hundreds of ports are still a tedious work and prone to human errors.  Some Catalyst commands need to be translated into NX-OS commands.

3) Port-mapping is another process prone to human error.  Cabling team might tell you the cable from Catalyst-Switch-23 port G3/27 is going to be moved to FEX-Switch-19 port 11.  If the cabling team fat-fingered the FEX port number, network team could overwrite a FEX port that is currently being used and cause an outage.  Sure you may review the FEX port before applying the changes.  But again, reviewing hundreds of ports is a tedious work.

4) Due to project schedule, cabling team has to build the port-mapping even before the FEX was online at N5K.  Thus they reference the FEX by their grid location (e.g. "AB23") versus the "FEX number" in N5K (e.g. "Ethernet101").  How do we build the configuration script with mapping table referencing grid numbers?


Spreadsheet is a very useful tool because:
  • (Almost) everyone has a spreadsheet application on their computer (Microsoft Excel)
  • Spreadsheet is easy to use and format data, even the user is not very computer savvy (such as the cable guys)
  • Formulas can be used to validate data and generate desired results

I asked server team provide us a spreadsheet with servers they want to migrate in the first phase.  Each row of the spreadsheet contains server IP address, subnet mask, default gateway, current switch name and switch port the server is connecting to.

I wrote a VB script to format the "show run" output from switches into Excel spreadsheet with switch name, switch port, and interface configuration.

By cross-referencing server team's spreadsheet and the "show run" spreadsheet (done by computer of course), I have a new spreadsheet that tells me what VLANs and what default GWs are required by the servers.  I review the configuration on new switches.  If VLANs or default GWs are not ready, I submit change request to create them.

This is just the preparation stage.  We haven't got to the FEX script stage yet.

Next is to build a script that translate the Catalyst commands into NX-OS commands in the "show run" spreadsheet.  (You may also do "find/replace".  But IMO, scripting is more flexible).

Next is to use a formula to translate the FEX grid number into N5K FEX numbers (i.e. from "AB23" to "Ethernet101").  Since we have more than one pair of N5K, this can't be done by simply "find/replace).  E.g. "AB23" is corresponding to "Ethernet101" on first pair of N5Ks.  However, "CD45" is corresponding to "Ethernet101" on the 2nd pair of N5Ks.  Excel VLOOKUP function can achieve this.

Next is to use a formula to build the FEX interface configuration.  As we need to look up both switch name and port number, Excel INDEX function is used.

Last but not the least, we also need to factor human errors.

1) For each server on the spreadsheet, we should have old switch name, old port number, new switch name and new switch number.  We cannot migrate the server if one of those was missing.  I build a column to validate this.  If something is missing, the value on corresponding row will be 'ERR'.  Then I can filter all 'ERR' rows by this column.

2) For each port we're migrating, there should be no existing config on the new switch (FEX).  If there's existing config, we might have a conflict.  I build another column to validate this.  Again, it'll generate 'ERR' if a port was already configured.  Then I can filter all 'ERR' rows by this column.

In summary, with VB script and spreadsheet formulas, I save 95% of the time and lower the risk of human errors.