#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!

Comments are closed.