Software Inventory View Sample Queries
The following sample queries demonstrate how the software inventory views can be joined to other views to retrieve specific data.
Joining Software Product and Software File Views
The following query lists all files that have been inventoried for SMS. The v_GS_SoftwareProduct and v_GS_SoftwareFile views are joined by using the ProductID columns.
SELECT DISTINCT SF.FileName, SF.FileDescription, SF.FileVersion
FROM v_GS_SoftwareProduct SP INNER JOIN v_GS_SoftwareFile SF
ON SP.ProductID = SF.ProductId
WHERE SP.ProductName = 'Systems Management Server'
ORDER BY SF.FileName
Joining Software Product, Software File, and Resource Views
The following query lists all inventoried products and the associated files for a computer with the NetBIOS name of COMPUTER1. The v_R_System and v_GS_SoftwareProduct views are joined by using the ResourceID column, and the v_GS_SoftwareProduct and v_GS_SoftwareFile views are joined by using the ProductID columns.
SELECT DISTINCT SP.ProductName, SF.FileName
FROM v_R_System SYS INNER JOIN v_GS_SoftwareProduct SP
ON SYS.ResourceID = SP.ResourceID INNER JOIN v_GS_SoftwareFile SF
ON SP.ProductID = SF.ProductId
WHERE SYS.Netbios_Name0 = 'COMPUTER1'
ORDER BY SP.ProductName
Joining Software Product, Software File, Resource, and Hardware Inventory Views
The following query lists all computers that have Microsoft Office 2003 installed and have less than 1 GB of free space on the local C drive. The v_GS_SoftwareFile and v_SoftwareProduct views are joined by the ProductID column, and the v_GS_LOGICAL_DISK and v_R_System views are joined to v_GS_SoftwareFile by using the ResourceID columns.
SELECT DISTINCT SYS.Netbios_Name0, SYS.User_Domain0, LD.FreeSpace0
FROM v_GS_SoftwareFile SF INNER JOIN v_SoftwareProduct SP
ON SF.ProductId = SP.ProductID
INNER JOIN v_GS_LOGICAL_DISK LD
ON SF.ResourceID = LD.ResourceID
INNER JOIN v_R_System SYS
ON SF.ResourceID = SYS.ResourceID
WHERE (LD.Description0 = 'local fixed Disk')
AND (SP.ProductName = 'Microsoft Office 2003')
AND (LD.FreeSpace0 < 1000)
AND (LD.DeviceID0 = 'C:')
Joining Software Product, Resource, and Software Metering Views
The following query lists all products that have been metered through software metering rules. The v_GS_SoftwareProduct and v_MeteredFiles views are joined by the ProductID column, and the v_GS_SoftwareProduct and v_R_System views are joined by using the ResourceID columns.
SELECT SYS.Netbios_Name0, SP.ProductName, SP.ProductVersion,
MF.FileName, MF.MeteredFileVersion
FROM v_GS_SoftwareProduct SP INNER JOIN v_MeteredFiles MF
ON SP.ProductID = MF.MeteredProductID INNER JOIN v_R_System SYS
ON SP.ResourceID = SYS.ResourceID
ORDER BY SYS.ResourceID, SP.ProductName, MF.FileName