Sample Queries for Software Inventory in Configuration Manager

The following query lists all software files for the Configuration Manager product that have been inventoried on Configuration Manager clients. 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 = 'System Center Configuration Manager' 
ORDER BY SF.FileName 

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 

The following query lists all computers that have Microsoft Office 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 LIKE 'Microsoft Office%') 
  AND (LD.FreeSpace0 < 1000) 
  AND (LD.DeviceID0 = 'C:') 

The following query lists all files that have been metered through software metering rules and sorted first by NetBIOS name, and then by product name, and then by file name. 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.Netbios_Name0, SP.ProductName, MF.FileName