Data Management

Workstation lifecycle and database help build a manageable inventory process

Creating an accurate equipment inventory can be a difficult undertaking for IT pros. Use this sample workstation lifecycle and relational database as a model to set up a better inventory process for your shop.

One of the most basic requirements for computer support staff—an accurate hardware inventory of all workstations—can be one of the most difficult things to implement and maintain. For IT pros, making sure that systems are secure and running smoothly and that no support issues stand in the way of business productivity is paramount. So it's not surprising that documentation or database updates that must be performed manually are neglected, making the information quickly out of date and essentially useless.

To make it easier to stay on top of inventory tasks, I created a basic office workstation lifecycle model. It provides a bird's-eye view of how the computers you deploy and support are used inside your organization. Further, I've created a sample relational database diagram that details how you can organize information about the equipment you support. Read on for a description of the model and database, and the method of using both to create an accurate inventory for your tech team.

Organizing inventory information
Among the most important first steps in creating an accurate hardware inventory of all workstations is modeling their typical lifecycle. Once you've done this, it becomes much easier to identify the various states these computers can find themselves in. This, in turn, will allow you to define the formal business processes you need to bring them from one state to the other without gradual degradation of information.

My basic office workstation lifecycle model, shown in Figure A, may not be universal. But it can serve as a useful starting point if you're currently looking for a broad view of how the computers you deploy and support are used inside your organization. Once you've shaped this model into an accurate lifecycle for your environment, you’ll end up with two things: identification of every state a computer can be in at any given time and an overall description of the process that will bring them from one state to another. The key to maintaining accurate inventory information will be to make sure your database model matches your custom lifecycle and to automate the state-altering processes as much as possible.

Figure A

Elements of the lifecycle
The lifecycle is split into these five categories:
  • Procurement
  • Inventory
  • Deployment
  • Change management
  • Retirement

Excluding the first and last states, each end state of one category is equal to the start state of the next. The ovals represent the processes that will alter the state of a given computer. The title of each one constitutes your starting point to documenting the necessary steps to complete the state change. An important aspect of this documentation is classifying the steps as either automatable or unautomatable and then selecting the appropriate technology to support the tasks you plan on automating.

For example, if one of the first things you do when you receive new computers is apply some standard OS image—or if they come with the image preinstalled—you could easily write a VBScript that interfaces with Windows Management Instrumentation (WMI) to automatically pick up system information. Microsoft’s script center has great examples you can build from.

This script could also interface with an ODBC-compliant database to upload the information to your inventory table, eliminating the need for the technician to manually enter this information. To push things one step further, the activation of this script can potentially be integrated into your automated workstation build procedure. Some information, such as the computer’s current location, cannot automatically be detected, but the same script could easily prompt the user for the necessary information and upload it to the same table.

As a rule, you should never have to enter the same information twice and you should never have to enter information manually that could be automatically detected. This will eliminate much of the human error associated with keeping a workstation inventory. If you're doing either of these things, your process should be optimized further.

Building the database
Figure B is a relational database diagram that depicts a basic but compatible model for the storage of this kind of information. The main table, Computer, stores all current information pertaining to your workstations. The Computer Change History table is basically made up of the individual transactions that individual PCs have undergone over the course of their lifetime. It becomes very easy at this point to generate a single computer’s chronological history simply by running a query against this table, limiting the displayed records to a single serial number, and ordering them by date.

The remaining tables act as safeguards to data integrity, ensuring that the values that can be enumerated are the only ones accepted. Therefore, the scripts I've produced so far in an effort to implement this model do two things: update the computer table and record the transaction in the Computer Change History table.

Figure B

An evolving system
Good programming practices become important when more than one script is used in the same context. For example, you should use variable names that match the names of the table fields you'll be dealing with. Although this may seem like a confusing approach, you’ll easily be able to tell the difference between direct references to table fields and your script variables because the latter will appear in SQL-related commands contained within quotation marks and the former will not. Using this convention across your scripts will make them much easier to read.

In addition, consider creating functions that produce information you require often. For example, much of the WMI requests for system information can easily be made into functions, again making the code a whole lot friendlier. You’ll also want to find a way to ensure that you can update all similar functions across your scripts at a stroke.

This method may not be entirely suitable for you, but at least it may provide some food for thought when you evaluate how your current workstation change control system can be further optimized. Remember the two golden rules: If you are manually entering detectable information in your databases or if you are entering the same information manually in two or more locations, your inventory maintenance overhead is not as low as it could be.

How do you handle your inventory?
Is your method of keeping a correct equipment inventory similar to this one? If not, how do you keep track? Post your advice in the discussion below.


Editor's Picks

Free Newsletters, In your Inbox