HOME | ABOUT US | INSIGHTS | CLIENT SERVICES | OUR ASSOCIATES | CONTACT US

Home > Insights > Frontiers > Lessons Learned

Insights | Frontiers | IP Address Planning and Inventory Tracking

We have developed an IP address planning and inventory tool as a spreadsheet template that we are making available for download here. You are welcome to use it and modify it for your own purposes, but we'd appreciate it if you would share with us any refinements you make to this tool. We would also like to hear if you find this useful, or other feedback. Send comments/feedback to IP-Addr-Plan@Interisle.net.

The tool itself works in a novel manner. Once the initial IP address is set, nearly everything else is controlled by entering CIDR values for subnets and supernets. If the CIDR value is 32, then the corresponding row is treated as a host record, and host numbers can be entered to track individual hosts and their IP addresses. If values are entered that violate CIDR rules for Variable-Length Subnet Masks (VLSM), then the error will be flagged by changing the background of the CIDR cell to red. Any subnet can be declared a "supernet" by setting a flag, which will then allow subsequent entries to define subnets within the supernet. This makes it relatively easy to perform supernetting and subnetting in an arbitrary manner, or as part of a planned IP subnet hierarchy.

There are two intended uses for this tool:

1. Planning out an IP network where blocks of IP addresses are treated as subnets and supernets. The ability to work with just the CIDR value simplifies data entry, and also helps detect errors or potential problems. No mental gyrations to convert between binary and dotted decimal notation are needed when using the tool in this manner.
2. Building an inventory of IP address assignments for an existing network. In this case, the existing subnets would be set up, and then host entries could be added as necessary under each subnet. Ranges of host addresses used for DHCP assignment blocks could also be documented. Extra columns could be added to keep track of things like MAC addresses, physical locations, switch ports, cable tags, devices, contact info, etc.

This tool is provided as an Excel workbook with six worksheet tabs. The first tab provides instructions and details on how to use or modify the tool. The second tab is a blank template for building IP address plans and inventories of IP nodes or subnets. The third tab provides a worked example that illustrates various ways to use this tool, and the fourth tab provides some tables that are used in some of the calculations and conversions. The final two tabs are handy "crib sheets" that can be used as reference tables when working with IP addresses. Note that the "Dot" and "Append_CIDR" variables are used to control how IP addresses are displayed. Other useful variables are explained on the Instruction sheet.

It should be relatively easy to use this spreadsheet with Open Office, or other applications that can import Excel files. If you do convert this to work with other non-Microsoft applications, we'd appreciate it if you would let us know and share the results. Note that we intentionally tried to keep the formulas as simple as feasible, and we avoided use of any extensions or libraries.

There is one major caveat worth noting: The spreadsheet approach is limited in what can be achieved when manipulating IP addresses and masks. In part, this is due to the awkward way that IPv4 addresses are defined. Consequently, this tool reflects compromises and trade-offs that were influenced by our intended uses. You may find that you can modify this tool to achieve your own trade-offs, or you may find that this is not the right tool for your needs. Since we like to promote open source developments, you may want to take a look at the IPplan tool, which is more robust than our simple spreadsheet. There are also several commercial tools that do a good job of solving an array of IP address management problems that would be especially useful for large networks. The spreadsheet tool provided here is a useful IP address calculator and IP address planner that can be used with small to moderate sized networks. With a bit of care, it could be extended to enterprise-wide networks.

Chuck Wade developed this tool based on nearly 15 years of occasional experience dealing with IP addressing problems for networks of all sizes. He has built numerous spreadsheets used to plan or track IP addressing schemes in the past, but this is the first one he actually likes.

Download version 1.01 as an Excel 2003/2004 document (~300 kB)