Sample queries for software inventory in Configuration Manager
The following sample queries demonstrate how the Configuration Manager software inventory views can be joined to other views to retrieve specific data. The software inventory views are typically joined to other views by using the ProductID, FileID, and ResourceID columns.
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 = '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