Share via


Sample Queries for Software Metering in Configuration Manager

 

Updated: January 1, 2014

Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager

The following sample queries demonstrate how to join the most common software metering views to other views.

Joining Software Metering, Software Inventory, and Discovery Views

The following query lists all resources that have run metered files, including the resource name, file ID, file name, file version, and start time. The v_MeterData software metering view is joined to the v_ProductFileInfo software inventory view by using the FileID column and to the v_R_System discovery view by using the ResourceID column.

SELECT SYS.Netbios_Name0, PFI.FileID, PFI.FileName, 
PFI.FileVersion, MD.StartTime 
FROM v_MeterData MD INNER JOIN v_ProductFileInfo PFI 
ON MD.FileID = PFI.FileID INNER JOIN v_R_System SYS 
ON MD.ResourceID = SYS.ResourceID 
ORDER BY SYS.Netbios_Name0, PFI.FileName 

Joining Software Metering, Status, and Software Inventory Views

The following query lists all users who have run metered files. The query returns the user domain, user name, file name, file version, usage count, total time of usage, and the last time the file was used. The v_MeteredUser software metering view is joined to the v_MonthlyUsageSummary status view by using the MeteredUserID column. The v_MonthlyUsageSummary status view is joined to the v_GS_SoftwareFile software inventory view by using the FileID column.

SELECT MU.Domain, MU.UserName, SF.FileName, SF.FileVersion, 
MUS.UsageCount, MUS.UsageTime, MUS.LastUsage 
FROM v_MeteredUser MU INNER JOIN v_MonthlyUsageSummary MUS 
ON MU.MeteredUserID = MUS.MeteredUserID INNER JOIN 
v_GS_SoftwareFile SF ON MUS.FileID = SF.FileID 
ORDER BY MU.Domain, MU.UserName, SF.FileName