SQL: All Collections with Deployment Count

This query returns a list of all SCCM Collections including Member Count, Deployment Count and folder location for each collection. It also includes the RefreshType which is useful for identifying collections that are set to Incremental Updates.

/* All Collections with Deployment Count */
SELECT DISTINCT coll.SiteID AS 'CollectionID', coll.CollectionName AS 'Collection Name', 
CASE
	WHEN coll.CollectionType = 0 THEN 'Unknown'
	WHEN coll.CollectionType = 1 THEN 'User'
	WHEN coll.CollectionType = 2 THEN 'Device'
END AS 'Collection Type'
,
coll.LimitToCollectionID, coll.LimitToCollectionName
,
CASE
	WHEN RefreshType = 0 Then 'None'
	WHEN RefreshType = 1 THEN 'Manual'
	WHEN RefreshType = 2 THEN 'Schedule'
	WHEN RefreshType = 4 THEN 'Incremental'
	WHEN RefreshType = 6 THEN 'Incremental + Schedule'
END AS 'Refresh Type'
, coll.MemberCount, count(ds.SoftwareName) AS '# Deployments', coll.ObjectPath AS 'Folder Path', 
CAST(coll.FullEvaluationLastRefreshTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME) AS 'FullEvaluationLastRefreshTime', 
CAST(coll.FullEvaluationNextRefreshTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME) AS 'FullEvaluationNextRefreshTime', 
CAST(coll.LastMemberChangeTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS DATETIME) AS 'LastMemberChangeTime',
coll.FullEvaluationRunTime / 1000 AS 'Eval Time (Seconds)', '' AS 'Notes'
FROM v_Collections AS coll LEFT OUTER JOIN
v_DeploymentSummary AS ds ON coll.SiteID = ds.CollectionID
GROUP BY coll.SiteID, coll.CollectionType, coll.RefreshType, coll.MemberCount, coll.CollectionName, coll.LimitToCollectionID, coll.LimitToCollectionName, coll.ObjectPath, coll.ObjectPath, coll.FullEvaluationLastRefreshTime, coll.FullEvaluationNextRefreshTime, coll.FullEvaluationRunTime, coll.LastMemberChangeTime
ORDER BY [Collection Type], coll.ObjectPath, coll.CollectionName

In addition to the above query, you can also use SCCM Management Insights to identify collections that may not be in an optimal state.

In the Configuration Manager Administrator Console navigate to Administration / Management Insights / All Insights / Collections and click on Show Insights. In here you will find insights for the following categories.

• Collections with no query rules and enabled for any schedule
• Collections with no query rules and incremental updates enabled
• Collections with no query rules and no direct members
• Collections with no query rules and schedule full evaluation selected
• Collections with query time over 5 minutes
• Collections with the same re-evaluation start time
• Empty Collections

Review each of these and remove/modify your collections appropriately.