Displaying Software Data in Views
The Software Inventory information for Client PCs is contained in the PKGRPTDB table. Each row represents an instance of a software package on a PC, and contains the package name, the path where the package was found, the date when the package was found, and the package status. This topic describes examples of how to display package information using the Database Viewer.
Passing SQL Statements in a Command Line
When passing a SQL statement from the command line interface or from a batch file, you must use two percentage characters ( %% ) in place of a single percentage character. (If you are editing the SQL details from within the Database Viewer, you need only use a single percentage character.)
Listing the PC, Department, Drive and Found Date for an Application
SELECT NODES.NODENAME, NODES.DEPT, PKGRPTDB.PKGDEVICE, PKGRPTDB.FOUNDDATE
FROM NODES INNER JOIN PKGRPTDB ON PKGRPTDB.NODENAME = NODES.NODENAME
WHERE PKGRPTDB.PACKAGE LIKE 'MS Word%'
AND PKGRPTDB.PKGSTATUS NOT LIKE 'Removed';
Checking the Package Status
The Status field (PKGSTATUS) can be used to identify packages that have been removed. Possible values for this field are as follows:
Status Value |
Description |
Removed |
the package has been uninstalled |
Package |
The package has been installed on the PC since the previous inventory |
Normal |
the package is unchanged since the last inventory |
NewVersion |
a newer version of the package has been installed since the previous inventory |
Using Correct Package Names
Ensure you are using the correct package names when specifying them as criteria for the PKGRPTDB.PACKAGE field. For a full list of package names, use the Packages Database window in the Console, or look at the PACKAGES table in the Database.
To Select All WinZip PCs and Users
SELECT NODES.NODENAME, NODES.OWNERNAME
FROM NODES INNER JOIN PKGRPTDB ON PKGRPTDB.NODENAME = NODES.NODENAME
WHERE PKGRPTDB.PACKAGE LIKE 'WinZip%'
AND PKGRPTDB.PKGSTATUS NOT LIKE 'Removed';
Interrogating the Packages Table
To see a list of all Microsoft packages which can be identified at your Site:
SELECT PACKAGES.PACKAGE, PACKAGES.PKGVERSION, PACKAGES.PKGTYPE
FROM PACKAGES WHERE PACKAGES.PACKAGE LIKE 'MS%'
Identifying PCs In Need of a Software Inventory
To identify Clients for which there is no software inventory data:
SELECT NODES.NODENAME, NODES.DOMAINNAME FROM NODES
INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME
WHERE NODEPROP.PROPNAME = '$SWDATE'
AND NODEPROP.PROPVALUE IS NULL;