The CLC Inventory system is an ever-expanding collection of databases, custom software and web pages used to keep track of our lab and classroom computers and the networks and servers that support them.  Most of the information is for internal use, but end-users can see results like our Lab Hours and Locations page and Current Lab Use page.  The main database has, as of 11/2005, 90 tables, 101 views, 69 indexes, 71 stored procedures, and 39 user functions.

Various subsystems include the following, with counts for "today" being 11/17/05.

Locations

Everything is located in a room in a building.   Today we have 348 rooms listed in 85 buildings and a separate location manager to add buildings and rooms.  There are also room schedules associated with classrooms; that data is pulled from the data warehouse and reformatted for use by our Lab Hours and Locations page.

Computers

We have two kinds of data on our lab and classrooms computers; one set is what is deployed by seat or IP address; different pieces of hardware might occupy the same spot over time.  Another group is tied to the individual hardware; computers get moved around so this information is by serial number and MAC address.  Today there are 2707 deployed computers (2065 Windows, 577 Macintosh, 65 Linux).  The detail inventory of Windows machines has 2123 records, so some are out for repair or in storage or otherwise not assigned to a room.

There are also 69 records in a Servers table.   Windows computers and servers are updated from querying Microsoft's Active Directory.

Hardware and Software

All Windows XP computers are inventoried every night with our custom software using the WMI (Windows Management Interface) and data about all the hardware parts and software installed is updated.  There are tables with data on boot devices, chassis intrusions, disks, displays, memory, network cards, processors, scanners, software packages and video cards.

Technology Classroom Inventory

In addition to computers, we collect data on equipment in technology classrooms such as radio mice and receivers, video switches, zip drives, USB and Firewire hubs, DVD/VCR players, projectors and projector bulb life.

Networks

Data about network switches, switch ports, and subnets is in the the database.   Today we have 94 network switches in 33 locations (in many buildings TNS runs the networks).  We also have a table for the UPS's (Uninterruptible Power Supplies) that keep the network switches up when the power goes off.   Today there are 185 records in the subnets table.  We use that data along with numerous other tables to check, every day, that every IP address that is in use in all our subnets is assigned to something.

There is another table of lab computer address ranges, defining IP addresses that may be used by different platforms in different rooms.  Ranges used in the past are retained to help reconstruct historical lab use data.   Today there are 673 address ranges defined.

Printers

Printers, printer models, printer commands, print servers, and print queues are all defined in the database.  Today there are 181 printers of 15 models.  (Records of all print jobs are in the Pals database.)

Repairs

The Repairs subsystem tracks computers, printers, and other equipment needing repairs or in the process of being repaired.  About 1000 records were entered this year.

This isn't everything in our inventory database, but it is most of what I can think of now.

Development

The evolution of the database has been admittedly haphazard.   We tried several times to make a single grand design, but there were too many pieces.  So we just started building it and adding things as we needed them.  This works ok for a small development group but it is a bit messy.