Tag : sql query

More Accurate SCCM Software Metering Report

I found out that the sccm table view v_MonthlyUsageSummary is not really accurate, actually v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0 is the best accurate one to determine user’s application usage by SCCM:

here is my SQL query:

SELECT DISTINCT
TOP (100) PERCENT RV.AD_Site_Name0 AS [AD Site], RV.Netbios_Name0 AS [PC Name], RV.User_Name0 AS [User Name], dbo.v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0 AS [Last Used Time],
dbo.v_GS_CCM_RECENTLY_USED_APPS.OriginalFileName0 AS [File Name], dbo.v_GS_CCM_RECENTLY_USED_APPS.ProductName0 AS [Product Name],
dbo.v_GS_CCM_RECENTLY_USED_APPS.ProductVersion0 AS Version, dbo.v_R_User.description0 AS [Employee Location], dbo.v_R_User.Mail0 AS Email, dbo.v_R_User.title0 AS Title,
dbo.v_R_User.department0 AS Department, dbo.v_R_User.manager0 AS Manger, RV.Creation_Date0 AS [PC Joined Date]
FROM dbo.v_R_System_Valid AS RV INNER JOIN
dbo.v_GS_CCM_RECENTLY_USED_APPS ON RV.ResourceID = dbo.v_GS_CCM_RECENTLY_USED_APPS.ResourceID INNER JOIN
dbo.v_R_User ON RV.User_Name0 = dbo.v_R_User.User_Name0
GROUP BY RV.Netbios_Name0, RV.AD_Site_Name0, RV.Creation_Date0, dbo.v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0, dbo.v_GS_CCM_RECENTLY_USED_APPS.OriginalFileName0,
dbo.v_GS_CCM_RECENTLY_USED_APPS.ProductName0, dbo.v_GS_CCM_RECENTLY_USED_APPS.ProductVersion0, RV.User_Name0, dbo.v_R_User.department0, dbo.v_R_User.description0,
dbo.v_R_User.Mail0, dbo.v_R_User.manager0, dbo.v_R_User.title0
HAVING (dbo.v_GS_CCM_RECENTLY_USED_APPS.OriginalFileName0 = N’WINPROJ.EXE’) AND (DATEDIFF(day, dbo.v_GS_CCM_RECENTLY_USED_APPS.LastUsedTime0, GETDATE()) > 30)
ORDER BY [Last Used Time]