In this post I am going to share SQL query (for reporting purpose). This can be used to fetch the data for applications which are not 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:

SQL Query – Specific application not used in last 90 days

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