Managing Network IP Assignment with a Database and Web Interface

Jim McBride

The Garvan Institute of Medical Research is a growing organization with about 350 researchers and support staff, and roughly the same number of computers throughout the building. We recently ran out of IP addresses within the two subnets we operated, so we launched a project to remodel the IP address space and reconfigure each computer with a new IP address. This required a visit to each computer in the organization -- an arduous task that we prefer to avoid.

Our IT group is the appropriate size for a publicly funded medical research group -- meaning that we have the bare minimum number people to run the operation. Reconfiguring each computer with a new IP address would add up to about 30-40 hours work. Since we had been assigning IP addresses directly to computers as they arrived, there was no way to avoid manually configuring each computer. Asking the researchers to adjust their own IP control panels was out of the question. Thus, we wanted to make this the last time we would have to take this manual approach.

The practice of statically assigning an IP address to each computer in the network also had drawbacks we wanted to remove. The most significant problem of statically assigned IP addresses is the need to keep an accurate database (or even just a simple list) of IP addresses in use and free IP addresses available to assign new computers. We found that the list of IP addresses would become out-of-date over time and we would assign a pre-existing IP to a newly arriving computer. This usually happened when the IP address was assigned to an offsite laptop computer, so the usual ping test to see whether the IP address was in use provided a false-negative response.

The final goal of the project was to increase network security. We were about to upgrade an aging set of 10-MB hubs for 100-MB Cisco switches. The Cisco gear allowed us to assign computers into VLANs and define access rules in and out of each VLAN. In this way, we could make the majority of our user's computers invisible to the Internet and therefore more secure from network attack, leaving only the public server equipment exposed to the Internet. We planned six VLANs with specific access rules for the types of computers they contain. For us, a VLAN is defined to be a set of contiguous IP addresses. So, again, good management of the IP address space, and the association of specific IP addresses to specific computers, became increasingly important.

Our Solution

We wanted the centralized address management offered by DHCP, but for security reasons, we wanted to associate a specific computer with a specific IP address. Our solution was to assign a fixed IP address to each computer through DHCP. The computer would retain the fixed address, but it would discover that address each time it rebooted and requested a session IP from the DHCP server. We assigned an IP address to the computer by associating the IP address with the computer's MAC address in the DHCPD.CONF file. The DHCP.CONF file therefore contained an up-to-date list of device to IP address mappings.

If we wanted to alter an address, we could change the IP address assigned to the computer in DHCPD.CONF, restart DHCP, and tell the user to reboot. After the initial setup, we could manage the IP configuration without ever having to visit the client, but the system still provided the equivalent of static address assignment by maintaining a persistent association of an IP address with a specific device.

When I thought about the process of visiting each computer and resetting it for DHCP, it involved several steps:

1. Record the computer MAC address.

2. Record the computer details (operating system, disk size, network patch point, VLAN membership, etc.).

3. Update control panels to use DHCP for IP.

4. Allocate an appropriate IP address from a list of free IP addresses.

5. Edit DHCPD.CONF with the MAC address and the newly allocated IP address.

6. Restart DHCP.

7. Reboot the computer.

8. Confirm the computer obtains the correct IP address and is operational.

Given that all computers on the network needed to be cut over in a short period of time, we wanted to avoid manual edits of DHCPD.CONF and hand-written IP address lists. We planned to use the Web and a browser interface to manage a database of computer details. We already operated several Sybase databases for research applications, and a number already had PHP pages written allowing information management via a browser. We set up a table in Sybase with the attributes shown in Table 1.

We presented this Sybase table via PHP code to a browser (see Figure 1). Any member of the IT Group can authenticate and access this system. The PHP code allows the authenticated IT staff member to add and edit records in the Sybase Computers table. The IT staff member fills in all the fields from AssetLabel, Room, Computer, and so on through to DHCPOn and Patch, with the exception of IPNet, IPSubNet, and IPNode. The PHP code captures all these entered attributes and, using the VLAN selected, executes the SQL code to determine the next available free IP address for that VLAN (see Listing 1, ipassign.php). The fields IPNet, IPSubNet, and IPNode are set and the record saved to the database. That IP number is no longer free, but is associated with the MAC address of the computer being connected to the network. The IP address cannot be reassigned until the Sybase record is later edited and the IP address is deleted or changed. The IT Group no longer must consult lists of allocated and free IP addresss. The PHP code looks after it all.

If the computer leaves the network, such as when an employee leaves and takes his personal laptop with him, the IT Staff member simply logs into the Web page, locates the computer, and removes the assigned IP address from the database record. If the computer moves from one VLAN to another, the IT Staff member logs into the Web page, locates the computer, and selects the new VLAN from the dropdown list. The PHP code again determines the next free IP number in that VLAN range and assigns it to the database record. The old IP immediately becomes free and can be assigned by the system at a later date.

1. Shows up at a computer

2. Determines the MAC address and other relevant information

3. Goes to the configuration page through the browser interface

4. Enters the data. (Does the PHP script generate some of the settings?) Clarify.

5. Selects a VLAN for the computer from a dropdown list

6. The PHP code completes the table by assigning an unused IP address associated with the VLAN.

If the computer is moved or joins a different VLAN, the IT staff member modifies the configuration through the browser.

Since DHCP is running from the same server that hosts Sybase, Apache, and PHP, it became a simple task to add PHP code to generate the DHCPD.CONF file from the Sybase table and restart DHCP (see Figure 2). The Sybase table had become the source of the DHCPD.CONF file. If there are any errors in the Sybase table, they become immediately apparent when DHCP restarts and the computer is restarted and does not get the correct IP address. This prevents discrepancies between the Sybase records and the real world.

What We Did

We set up the table in Sybase, wrote the PHP code to manage the table and to recreate DHCPD.CONF and restart DHCP. The IT Group then scattered through the building, visiting each computer and performing the steps outlined above. The project ran smoothly to completion. We migrated about 350 computers from static IP address allocation over to DHCP control, generating a current register of all computer and printer hardware on the network at the same time.

Now when a new computer is to be commissioned, or a computer is relocated or moved between VLANs, any member of the IT Group can access this system, update the Sybase record, and implement the changes into DHCP -- all from a browser. We often use the browser running on the computer that is being moved or updated. We also use browsers on computers out in the research laboratory areas rather than having to run back to our offices.

Other Benefits

The major benefits of this system are the management of the IP address space, the up-to-date record we now have of all computers and printers connected to the network, and we no longer must visit each computer on the network.

We've experienced other benefits from introducing an improved information management system. We are part of the Australian Academic Research Network (AARNET), providing our Internet connectivity through our association with the good people at the University of New South Wales. We are billed quarterly in one lump sum for usage based on the number of connected devices, and the traffic generated by each device. As an Institute, we agreed that these charges should be passed on to the various research groups. I needed to take the quarterly invoice and assign costs to each research program based on their actual contribution to the total invoice amount. I was able to obtain an online copy of the invoice, which includes a line for each IP address with its cost of traffic. I created a Sybase table to store the details of the quarterly bill. I wrote a Sybase stored procedure to take each research group, identify all computers operated by the group, sum the cost for the IP address of each computer, and produce a report. This report effectively becomes an invoice that we use to recover the costs from the research groups.

We also now have the ability to track software loaded on computers. We added several tables to the database to record purchased software, including version and number of licenses purchased. We added a page to the Web interface to allow IT staff to update these details as software is installed. In this way, we also have a method of tracking what software is installed, where, and how many licenses are in use.

Jim McBride gained a BSc from Sydney University then worked in the petroleum and insurance industries before moving to medical research. For the past seven years, Jim and the rest of the IT team have managed the computer and communication systems for the Garvan Institute of Medical Research. As well as delivering good network services, Jim and the team have designed an information architecture that now underlies in-house applications that scientists at the Garvan Institute use to meet the challenges of medical research.