Category Archives: SQL

#SQLSunday – Find Device Collections and Collection Membership

I try to use the ConfigMgr console as little as possible these days (long story).  So, here’s a SHINY and FUN thing you can do in SQL!

Want to know what device collection an endpoint belongs to?

select distinct
v_FullCollectionMembership.CollectionID As ‘Collection ID’
, v_Collection.Name As ‘Collection Name’
, v_R_System.Name0 As ‘Machine Name’
from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
–Uncomment below if you want to be specific.  You can use ‘AND’ operator if you want multiple specific systems returned.
–Where v_R_System.Name0=’MDT01′

Here's the collections my MDT server currently belongs to.

Here’s all the collections my MDT server currently belongs to in this environment.

Alternatively, want to return all the rows?

select * from v_FullCollectionMembership
where name =’MDT01′

And the most useful I’ve found from that view for reports is:

select distinct
v_FullCollectionMembership.CollectionID As ‘Collection ID’
, v_Collection.Name As ‘Collection Name’
, v_R_System.Name0 As ‘Machine Name’
,Domain
,SiteCode
,IsClient
,IsVirtualMachine
,VMHostName
from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
–Uncomment below if you want to be specific.  You can use ‘AND’ operator if you want multiple specific systems returned.
–Where v_R_System.Name0=’MDT01′

Very SHINY columns are returned when running this query!

Very SHINY columns are returned when running this query!

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