Displaying Hardware Data in Views

Vector AM collects comprehensive hardware data from desktop PCs. This includes items such as the CPU type and speed, the amount of memory, Service Packs and build information, the network card and address. This topic describes examples of how to display this information using the Database Viewer.

Passing SQL Statements in a Command Line

When passing a SQL statement from the command prompt interface or from a batch file, you must use two percentage characters ( %% ) in place of a single percentage character so that it will be passed to the Database Viewer correctly. A single percentage character will be ignored. (If you are editing the SQL details from within the Database Viewer, you need only use a single percentage character.)

Using the NODES Table

The NODES table in the Site database provides an easily accessible method of retrieving high-level Hardware information. SQL statements that refer only to this table are simple to construct and read. For example, to retrieve the Operating System of the Client named SB:

SELECT NODES.NODENAME, NODES.OSNAME FROM NODES WHERE NODES.NODENAME LIKE 'SB'

To identify all Clients with unknown processor types:

SELECT NODES.NODENAME, NODES.DEPT FROM NODES WHERE CPU IS NULL

To retrieve the major details for Clients that do not have Pentium processors:

SELECT NODES.NODENAME, NODES.MAKE, NODES.MODEL, NODES.CPU, NODES.CPUSPEED, NODES.TOTMEMORY FROM NODES WHERE CPU NOT LIKE 'Pentium%'

Retrieving Hardware Data from the HWMAIN Table

If you want to access more detailed hardware data, use the HWMAIN table. It provides a summary of information from other hardware inventory tables (prefixed with the letters HW, such as HWSHARE and HWMEMORY) in the Site database. Note that HWMAIN table can hold multiple records for each Client PC because it also records previous inventory results. To retrieve only the latest results, you must join the HWMAIN table with the HWUPDATE table. (The HWUPDATE table stores only one record per Client.) The record from the HWMAIN table must share the same PC name and last inventory date as a record in the HWUPDATE table. See the examples below.

To retrieve a list of PCs which have an S3 video driver:

SELECT HWUPDATE.NODENAME FROM HWUPDATE
INNER JOIN HWMAIN ON HWUPDATE.NODENAME = HWMAIN.NODENAME
AND HWUPDATE.HWDATE = HWMAIN.HWDATE
WHERE HWMAIN.SCREENTYPE LIKE 'S3%'

To find out what video driver type is used by the Client DB:

SELECT HWMAIN.NODENAME, HWMAIN.SCREENTYPE FROM HWMAIN
INNER JOIN HWUPDATE ON HWUPDATE.NODENAME = HWMAIN.NODENAME
AND HWUPDATE.HWDATE = HWMAIN.HWDATE
WHERE HWUPDATE.NODENAME="DB"

Retrieving Network Address Details

This statement returns network address details such as IP Address and MAC Address for all Clients:

SELECT HWMAIN.NODENAME, HWMAIN.IPADDRESS, HWMAIN.IPXADDRESS,
HWMAIN.MACADDRESS, HWMAIN.SUBNETMASK
FROM HWUPDATE INNER JOIN HWMAIN
ON HWUPDATE.NODENAME = HWMAIN.NODENAME
AND HWUPDATE.HWDATE = HWMAIN.HWDATE

Retrieving Monitor and Graphics Card Information

This statement returns video card, monitor and screen resolution details from the HWMAIN table:

SELECT HWMAIN.NODENAME, HWMAIN.SCREENTYPE, HWMAIN.VIDEOMEM, HWMAIN.XRES,
HWMAIN.YRES, HWMAIN.MONITORTYPE, HWMAIN.COLRDEPTH, HWMAIN.REFRESH
FROM HWUPDATE INNER JOIN HWMAIN
ON HWUPDATE.NODENAME = HWMAIN.NODENAME
AND HWUPDATE.HWDATE = HWMAIN.HWDATE

Discovering when Hardware Data Was Generated on a PC

You can refer to the HWUPDATE table to establish when Client PC data was generated. To do this, use the following SQL statement, amending the NODENAME value (shown as ADMIN4 in this example):

SELECT HWUPDATE.NODENAME, HWUPDATE.CREATEDATE FROM HWUPDATE
WHERE HWUPDATE.NODENAME = 'ADMIN4'

Identifying PCs Which Need a Hardware Inventory

To identify Clients for which there is no hardware inventory data:

SELECT NODES.NODENAME, NODES.DEPT FROM NODES
INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME
WHERE NODEPROP.PROPNAME = '$HWDATE' AND NODEPROP.PROPVALUE IS NULL

Windows 2000 PCs with Less than 64MB RAM

To identify Clients running Microsoft Windows 2000 on less than 10MB RAM:

SELECT DISTINCT NODES.NODENAME, PKGRPTDB.PACKAGE, HWMAIN.TOTALRAM
FROM (NODES INNER JOIN PKGRPTDB ON NODES.NODENAME = PKGRPTDB.NODENAME)
INNER JOIN (HWUPDATE INNER JOIN HWMAIN ON (HWUPDATE.HWDATE = HWMAIN.HWDATE)
AND (HWUPDATE.NODENAME = HWMAIN.NODENAME)) ON NODES.NODENAME = HWUPDATE.NODENAME
WHERE (((PKGRPTDB.PACKAGE) LIKE 'Windows 2000%')
AND ((PKGRPTDB.PKGSTATUS) NOT LIKE 'Deleted')
AND ((HWMAIN.TOTALRAM) < 63));