SQL Report: Unused Applications SCCM

This post is inspired by System Center Dudes post on auto uninstalling unused apps in your environment. It also has the same requirements (aka have Software Metering working in your environment if you want actual usage stats vs is this thing installed or not.)

Disclaimer: I’m non-technical so these SQL queries come without warranty.  The good news is it’s only reads, so you’re not destroying anything 🙂

System Center Dudes post uses WQL to create device collections that allow you to gather info on specific installed applications and also to be able to uninstall a specific application if it hasn’t been used within a certain time frame (120 days is good enough for me!) it’s an awesome idea, but I simply want to report on the presence of software and said usage to help management decide if we should buy the same amount of licenses for a specific software at renewal time.

Here’s what I came up with:

--Specific Product Installed
select Name0 as ComputerName, Resource_Domain_OR_Workgr0 as 'Domain/WorkGroup', Client0
from
dbo.v_r_System
inner join v_GS_INSTALLED_SOFTWARE on v_GS_INSTALLED_SOFTWARE.ResourceID = v_r_system.ResourceID
where
v_GS_INSTALLED_SOFTWARE.ProductName0 like 'Stata%'


--Usage count Last 120 Days
select Name0 as ComputerName, Resource_Domain_OR_Workgr0 as 'Domain/WorkGroup', Client0
from
dbo.v_r_System
inner join v_MonthlyUsageSummary
on v_R_System.ResourceID = v_MonthlyUsageSummary.ResourceID
inner join v_MeteredFiles
on v_MonthlyUsageSummary.FileID = v_MeteredFiles.MeteredFileID
where
v_MeteredFiles.ProductName like 'Stata%' AND DATEDIFF (day, v_MonthlyUsageSummary.LastUsage, GetDate()) < 120

Comments are closed.