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