SQL Query : For Summarizing Software Metering Usage

The first query brings back separate rows for each line in V_MonthlyUsageSummary view.

Second query consolidates it to one row and totals some of the fields.

/****** Software Metering - Multiple Rows  ******/
 SELECT MRT.ProductName, VRS.Name0, MUR.FullName, MFS.MeteredFileName, MUS.UsageCount, MUS.UsageTime, MUS.LastUsage, MUS.TimeKey AS FirstUsed
 FROM V_MonthlyUsageSummary MUS
 JOIN v_R_System VRS ON MUS.ResourceID = VRS.ResourceID
 JOIN v_MeteredUser MUR ON MUS.MeteredUserID = MUR.MeteredUserID
 JOIN v_MeteredFiles MFS ON MUS.FileID = MFS.MeteredFileID
 JOIN MeterRules MRT ON MRT.RuleID = MFS.RuleID
 WHERE MRT.ProductName = 'metering rule name' And (VRS.operatingSystem0 like 'Windows 7%' OR VRS.operatingSystem0 like 'Windows 8%' OR VRS.operatingSystem0 like 'Windows 10%')
 --and name0 = 'computer name'
 ORDER BY Name0

/****** Software Metering - Consolidated  ******/
 SELECT MRT.ProductName, VRS.Name0, VRS.operatingSystem0, MUR.FullName, MFS.MeteredFileName, sum(MUS.UsageCount) AS UsageCount, round(sum(MUS.UsageTime)/60,0)+1 AS UsageTime, max(MUS.LastUsage) AS LastUsed, min(MUS.TimeKey) AS FirstScanned
 FROM V_MonthlyUsageSummary MUS
 JOIN v_R_System VRS ON MUS.ResourceID = VRS.ResourceID
 JOIN v_MeteredUser MUR ON MUS.MeteredUserID = MUR.MeteredUserID
 JOIN v_MeteredFiles MFS ON MUS.FileID = MFS.MeteredFileID
 JOIN MeterRules MRT ON MRT.RuleID = MFS.RuleID
 WHERE MRT.ProductName = 'metering rule name' And (VRS.operatingSystem0 like 'Windows 7%' OR VRS.operatingSystem0 like 'Windows 8%' OR VRS.operatingSystem0 like 'Windows 10%')
 --and name0 = 'computer name'
 GROUP BY MRT.ProductName, VRS.Name0, VRS.operatingSystem0, MUR.FullName, MFS.MeteredFileName
 --ORDER BY UsageCount ASC
 ORDER BY Name0

Sample Output

 

Related posts

One Thought to “SQL Query : For Summarizing Software Metering Usage”

  1. SCCM_Buff

    I am trying to compare report from your value with the default report “Computers that have a metered program installed but have not run the program since a specified date ”
    but the default report has very fewer values than urs and someone the values are missing.
    Missing = the values which are present in default report are not present when I run your query.

Leave a Comment