Collections in Configmgr play a crucial role .If you want to do anything(can be software deployment,OSD ,Client agent settings,Software updates,compliance etc) in configmgr against clients,you a collection .
Being Configmgr administrator, it is always important to look at collection performance ,if they are scheduled well and do some maintenance like identifying collections that take longer time to update (collection evaluation viewer tool from Configmgr 2012 toolkit) or identifying the collections that update too frequently than expected etc.
This blog post will assist you to identify collections with count of direct membership rules and type of schedule and other important collection Queries which can be represented in Nice SSRS Report.
The below SQL Code is for Collections with count of Direct Membership rule and what type of Collection Schedule configured.
Usually for Collections with Direct membership rule, you really no need to configure any Schedule at all as they are one time created and do not require any update.
so ,you can simply run this SQL Code ,if the count of Direct membership rule is bigger and if any Schedule configured to get the RID of it.
Direct membership rule info stored in v_CollectionRuleDirect View ,For more information about SQL Views in Configmgr, refer this Excel spreadsheet
Schedule can be of anything listed below:
Scheduled and Incremental
select coll.CollectionName,crd.CollectionID,COUNT(crd.RuleName) [Direct rules],
Case when coll.RefreshType = 1 then 'Manual'
when coll.RefreshType = 2 then 'Scheduled'
when coll.RefreshType = 4 then 'Incremental'
when coll.RefreshType = 6 then 'Scheduled and Incremental'
else 'Unknown' end as RefreshType
from v_CollectionRuleDirect CRD
inner join v_Collections Coll on CRD.collectionID=coll.SiteID
Group by crd.CollectionID,coll.RefreshType,coll.CollectionName
order by crd.CollectionID
SQL Code for All Collections with its Refresh Type:
Select (Case when RefreshType = 1 then 'Manual'
when RefreshType = 2 then 'Scheduled'
when RefreshType = 4 then 'Incremental'
when RefreshType = 6 then 'Scheduled and Incremental'
else 'Unknown' end) as RefreshType, count(SiteID) as Collections
group by RefreshType