Category: SCCM

Here is a SQL query to build a report showing a specific application not been used for last 90 days. I have used Microsoft Word as an example. You need to make sure the winword.exe is enabled as Software meter rule in your SCCM environment. You may modify based upon your requirement and better to use it with parameter so that you can have a more powerful way to select from list of application:

SELECT vr.name0,
MU.UserName,
vu.full_user_name0,
SF.FileName,
SF.FileVersion,
MAX(MUS.LastUsage) as “LatestDate”,
GETDATE() as “CurrentDate”,
DATEDIFF(day,MAX(MUS.LastUsage),GETDATE())
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
INNER JOIN v_r_system vr on mu.username=vr.user_name0
INNER JOIN v_r_user vu on mu.username=vu.user_name0
where sf.filename in (‘Winword.exe’)
group by vr.name0,MU.UserName,vu.full_user_name0, SF.FileName, SF.FileVersion
HAVING (DATEDIFF(day, MAX(MUS.LastUsage), GETDATE()) >90)
ORDER BY vr.name0,MU.UserName,vu.full_user_name0