Script Inventories Remote Computers and Writes the Information to a Database

 Executive Summary:

Computer_Inventory.vbs retrieves inventory information from remote computers and writes that information to a Microsoft Access database. To do this, the script uses Windows Management Instrumentation (WMI) objects to retrieve information from the remote computers and ActiveX Data Objects ( ADO) and OLE DB to create the database and populate it with data.

At my company, administrators often need to create reports detailing inventory information (e.g., OS version, OS serial number) about the remote computers they manage. To help them, I wrote a script, Computer_Inventory.vbs, that retrieves this information from remote computers and writes it to a Microsoft Access database. All the administrators need to do is create an input file that lists the computers they want information for, run the utility, then query the database that the script creates and populates.

Computer_Inventory.vbs uses Windows Management Instrumentation (WMI) objects to retrieve information from the specified remote computers. To create the database and populate it with data, the script uses ActiveX Data Objects (ADO) and OLE DB. OLE DB is part of Microsoft Data Access Components (MDAC), a group of Microsoft technologies that provide a uniform way for applications to access almost any data store. You can create OLE DB providers that access simple data stores, such as a text file or spreadsheet, or complex databases, such as a SQL Server or Oracle database. (For information about how ADO and OLE DB work together, see the Microsoft article "OLE DB/ADO: Making Universal Data Access a Reality" at http://msdn2.microsoft.com/en-us/library/ms811450.aspx.)

Like most other scripts, Computer_Inventory.vbs begins by declaring its variables and initializing its constants. Then, the main block of code runs. As Listing 1 shows, this code first creates a log file, then creates the database, which will have one table. If the database and table already exist, the script won't create them again. Instead, it simply displays a message letting you know that they already exist and moves on to the next task, which is opening the input file and reading in the remote computer's names or IP addresses.

For each remote computer, the script calls the Get_user_info function, which Listing 2 shows. This function accesses the remote computer and gathers the inventory information by querying WMI's Win32_OperatingSystem class. As callout A in Listing 2 shows, the function uses the class's Caption property to obtain the OS's name, CSDVersion property to obtain the installed service pack, SerialNumber property to obtain the OS's serial number, Version property to obtain the OS version number, and WindowsDirectory property to obtain the name of the Windows directory (e.g., C:\Windows). Note that according to the Win32_OperatingSystem class documentation (http://msdn2.microsoft.com/en-us/library/Aa394239.aspx), the Caption property is supposed to return the OS name and version number. However, in some instances, the Caption property doesn't return the version number. For example, it doesn't return the version number for the Windows Vista Home Premium edition. Because our administrators need to sort records by the OS version, the script uses the Version property to capture that information.

After the Get_user_info function returns the remote computer's inventory information to the main block of code, Computer_Inventory.vbs enters an If…Then…Else statement, as callout A in Listing 1 shows. In this statement, the script checks the SerialNumber variable's value to see whether it's greater than 0, which would mean the remote computer's information was successfully gathered. When the value is greater than 0, the script calls the addrectodb function to add or update the database with the computer's information and sets the SerialNumber variable's value to 0 in preparation for checking the next computer. When the value isn't greater than 0, the script writes to the log file an error message that notes the computer was either not found or offline.

The script repeats this process for each computer until all the computers in the input file have been processed. Computer_Inventory.vbs then closes the input file and quits. The database is now populated and ready to be queried.

Computer_Inventory.vbs runs on Windows XP, Windows 2000 Server, and Win2K Professional machines with Access and Windows Script Host (WSH) 5.7 installed. To verify the WSH version, run the command

cscript //Logo 

in the command-shell window. The first line shows the WSH version.

You can download Computer_Inventory.vbs and a sample input file named computer.txt from the Scripting Pro VIP Web site. Create a folder named Inventory on your hard disk (i.e., C:\Inventory) and place those two files in that folder. When the script runs, it will place the log file, Inventory.log, in that folder as well.

Before you run Computer_Inventory.vbs, though, you need to perform two operations. First, open computer.txt in a text editor such as Notepad and insert the computer names or IP addresses of the remote computers for which you want to gather information. Figure 1 shows a sample file. The rows starting with an asterisk (*) are comment lines. The script is designed bypass the comment lines, so you can use them for notes if desired.

Second, you need to customize the code in Listing 3 to your environment. There are two mandatory and two optional customizations. Callout B in Listing 3 highlights the two mandatory customizations. In the line

dbname = "inventory.mdb"

you need to replace inventory.mdb with your database's pathname. You must specify the absolute path (e.g., C:\myfolder\inventory.mdb). In the line

tablename = "Inventory"

you need to replace Inventory with the name of your table.

If you want use a pathname other than C:\computer.txt for the input file, you need to customize the code in callout A. In the line

filecomputer = ".\computer.txt"

replace .\computer.txt with the appropriate pathname. Similarly, if you want to use a pathname other than C:\inventory.log for the output file, customize the code in callout C. In the line

logfilename = ".\inventory.log"

replace .\inventory.log with the appropriate pathname.

To launch Computer_Inventory.vbs, open a command-shell window and run the command

cscript Computer_Inventory.vbs

The account you use to run the script must have the necessary permissions to access the remote computers listed in computer.txt.

— Magni Mauro, System Engineer