In a complex SCCM environment when you’re dealing with a large number of Distribution Point Groups it is always good to run a periodic audit. There is no out of box report so here are some reports I use.

1. List of all DP groups and group members:

select distinct sGroupName, SUBSTRING(NALPath,
CHARINDEX(‘\\’, NALPath) + 2,
CHARINDEX(‘”]’, NALPath) – CHARINDEX(‘\\’, NALPath) – 3 ) as [DP Name] from v_DistributionPointGroup where NALPath not like ”* order by sGroupName asc, “DP Name” asc

*) – the first record is always a blank ‘placeholder’

2. The list of Members of Specific DP Group – Secondary

select SUBSTRING(NALPath,
CHARINDEX(‘\\’, NALPath) + 2,
CHARINDEX(‘”]’, NALPath) – CHARINDEX(‘\\’, NALPath) – 3 ) as [DP Name] from v_DistributionPointGroup where sGroupName=@DPGroupName* and NALPath not like ” order by “DP Name” asc

*) Prompted value

3. List of all DP groups DP belongs to – Secondary

select sGroupName as [DP Group Name] from v_DistributionPointGroup where NALPath like ‘%’+@DPName+’%’ and NALPath not like ” order by “DP Group Name” asc

*) Prompted value

In some situations you may want to populate your collection with the members of all its subcollections. For example you want to assign the same maintenance window to several dynamically populated collections and to minimize the administrative overhead. If your child collection ID’s are for example ABC000A1 and ABC000A2, your parent collection query will be:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_ABC000A1) or SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_ABC000A2)