Using WMI for Inventory Management

Introduction
With Windows 2000, WMI (Windows Management Interface) has now blossomed into a full-fledged technology. In a Windows 2000 environment where WMI is standard, a script can be written to populate an inventory database by utilizing ADO, WSH and WMI. This document will explain how the script works and some information that can be gathered.

With Windows 2000, WMI (Windows Management Interface) has now blossomed
into a full-fledged technology. In a Windows 2000 environment where WMI
is standard, a script can be written to populate an inventory database by
utilizing ADO, WSH and WMI. This document will explain how the script works
and some information that can be gathered.

This article will present a script and a database — both of which can be
expanded upon to create an impressive inventory management system. The
script is broken into 5 subroutines. The script file is servinv.vbs
and the database itself can be downloaded as well.

The Script

The WSH script is written using
vbscript.  At the begining of the
script the global variables are declared. Following each variable is a comment
to explain why the value is needed.

Dim strComputerName     ' The Computer Name to be queried via WMI
Dim strWinMgt           ' The WMI management String
Dim objCon              ' A Connection Object for database connectivity
Dim objRS               ' A Recordset Object for database connectivity
Dim sProviderName       ' The OLE Provider Type
Dim iCursorType         ' The Cursor Type for the Recordset
Dim iLockType           ' The Lock Type for the Recordset
Dim sDataSource         ' The name and location of the database
Dim intCompID           ' A computer ID asssigned when added to the database
Dim intRam              ' The amount of RAM in the computer.

After the variables are declared the user is prompted for a
computer name. The computer information is appended to the winmgmts://
string, so the string can be used to query WMI on each of the computers.

strComputerName = InputBox("Enter the name of the computer you wish to query")
strWinMgt = "winmgmts://" & strComputerName &""

Once the computer information is added to the script, 5 subroutines are called.
Two of the subroutines manage the connection to the database, while the other
three query for various information and write the information to the database.

call subConnectionOpen
call subWriteComputerInfo
call subWriteDiskInfo
call subWriteIPInfo
call subConnectionClose
		

The first subroutine (subConnectionOpen) uses OLE DB to open a
connection to the Microsoft Access 2000 database. More information can be found on how to
do this by reading about data access methods and ADO.

subConnectionOpen

Sub subConnectionOpen
    Set objCon = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    sProviderName        = "Microsoft.Jet.OLEDB.4.0"
    iCursorType          = 1
    iLockType            = 3
    sDataSource          = "dbCompInfo.mdb"
    objCon.Provider = sProviderName
    objCon.Properties("Data Source") = sDataSource
    objCon.Open
End Sub

The first subroutine that writes information to the database is subWriteComputerInfo.
There are a number of things to note in this subroutine. Line 2 is where WMI
is queried for the general computer information. Lines 3-8 write the information
from the CompSys object to variables so the information can be loaded
into the database. To find out what other information can be extracted, refer
to the
WMI Information on MSDN. Lines 10-14 open up a recordset object that was
created in the subConnectionOpen routine. The most notable line is line 12 where
the source for the recordset is established. In this case it is the table tblComputer. One of the fields in this table is an
auto incrementing interger (“ID”). When a new record is added (line 15),
populated (lines 16-18) and updated (line 19) a unique ID is returned for the
newly added record (line 20) at this point the recordset can be closed.

Lines 23-32 mimic lines 10-21 with some exceptions. At line 25 the recordset has
a new source — in this case the table tblMemory. When a new record is added
to the table, the ID field is set equal to the computer ID that was just allocated in the
table tblComputer.

subWriteComputerInfo

1 Sub subWriteComputerInfo
2    Set CompSysSet = GetObject(strWinMgt).ExecQuery("select * from Win32_ComputerSystem")
3    for each CompSys in CompSysSet
4            strDescription = CompSys.Description
5            strModel       = CompSys.Model
6            strName        = CompSys.Name
7            intRAM         = Clng(CompSys.TotalPhysicalMemory/1000000)
8    next
9
10    objRS.CursorType = iCursorType
11    objRS.LockType = iLockType
12    objRS.Source = "tblComputer"
13    objRS.ActiveConnection = objCon
14    objRS.Open
15    objRS.AddNew
16    objRS("strName") = strName
17    objRS("strModel") = strModel
18    objRS("strDescription")= strDescription
19    objRS.Update
20    intCompID = objRS("ID")
21    objRS.Close
22
23    objRS.CursorType = iCursorType
24    objRS.LockType = iLockType
25    objRS.Source = "tblMemory"
26    objRS.ActiveConnection = objCon
27    objRS.Open
28    objRS.AddNew
29    objRS("intCompID") = intCompID
30    objRS("intRAM")= intRam
31    objRS.Update
32    objRS.Close
33
34 End Sub

This same methodology is used for the subWriteDiskInfo and the
subWriteIPInfo routines. In each case the differences are the same.
Once the appropriate WMI query is performed and the information extracted,
the information updates the appropriate table by changing the source of the
recordset and writing the data to the correct fields in the recordset.

Finally, the subConnectionClose routine sets
the recordset (objRS) object and the connection (objCon) objects to nothing.

subConnectionClose

Sub subConnectionClose
    set objRS = nothing
    set objCon = nothing
End Sub

The Database

The database used to support this script consists of four tables: tblComputer,
tblDisk, tblMemory, and tblIPInfo. The tblComputer contains an identity field
“ID” which increments automatically each time a record is added to the table. In
addition, a time stamp is inserted so the date the inventory was taken is
recorded.  Once the computer information has been entered, the ID is added
to the intCompID field that exists in each table.  This allows each piece of
information to be related back to the computer from which the informaiton was
acquired. 

Four reports in the database allow the information to be correlated and
output.  This is done by creating one main report and three
subreports.  The subreports are related to the main report by the ID field
in the rptComputer and the intCompID field in the subreports.

Conclusions

A WSH script using VBScript is explained. The script uses WMI to query a computer for some predetermined information. The acquired information is written to a database using ADO.   This routine should be able to be run on a Windows 2000 machine.  To run this routine on an NT 4.0 machine or a Windows 95 machine WMI has to be loaded.