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
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.