SCCM Report – Software Metering Report for Last Usage Over 30 Days Ago

Here is my SQL Statement for SCCM Report – Software Metering Report for Last Usage Over 30 Days Ago

 

SELECT DISTINCT
TOP (100) PERCENT RV.AD_Site_Name0, v_R_User_1.description0, RV.Netbios_Name0, RV.User_Name0, MAX(MUS.LastUsage) AS Expr1, OS.Caption0, RV.Creation_Date0, v_R_User_1.department0,
v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0, MF.MeteredFileName, MF.MeteredFileVersion
FROM dbo.v_R_System_Valid AS RV INNER JOIN
dbo.v_MonthlyUsageSummary AS MUS ON RV.ResourceID = MUS.ResourceID INNER JOIN
dbo.v_MeteredFiles AS MF ON MUS.FileID = MF.MeteredFileID INNER JOIN
dbo.v_R_User AS v_R_User_1 ON RV.User_Name0 = v_R_User_1.User_Name0 LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM AS OS ON RV.ResourceID = OS.ResourceID CROSS JOIN
dbo.v_R_User
WHERE (DATEDIFF(day, MUS.LastUsage, GETDATE()) > 30) AND (MF.RuleID = ? OR
MF.RuleID = ? OR
MF.RuleID = ? )
GROUP BY RV.Netbios_Name0, RV.User_Name0, OS.Caption0, RV.AD_Site_Name0, RV.Creation_Date0, v_R_User_1.department0, v_R_User_1.description0, v_R_User_1.Mail0, v_R_User_1.manager0, v_R_User_1.title0,
MF.MeteredFileName, MF.MeteredFileVersion

ORDER BY Expr1

Add your internal Metering Rule in ” MF.RuleID=?”

Leave a Reply