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:

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.

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.