Configuration Manager Collection Cleanup Suggestions

Certainly in your CurrentBranch Console, under "Management Insights", there are some things there regarding collection cleanup; but here's a few other ways to look at your data.

Over the years, Collection plaque and tartar just grows and grows... and over time, people forget what collections were made for, or why they have them.  As a way to help the people who use our console narrow it down a bit to 'possible' stale, old collections which no longer have any purpose, below is a potential starting point.

What the below would list is collectionids and names, which are:
- NOT Currently used for any other collection as a "limited to", "Include", or "Exclude"
- NOT Currently used for any Deployment, whether it's a baseline, an application, an advertisement, or a task sequence
- NOT Currently used to define a Service Window (aka Maintenance Window)
- NOT Currently used for any custom client agent settings you might have configured.
- NOT currently used for any collection variables you might have for OSD
- NOT currently used for Automatic Client Upgrade, as an excluded collection
- NOT a default/out of the box collection (aka, ones that start with SMS)

This isn't of course a definitive list.  For example, perhaps a collection was created to deploy "Really Important Application" 2 weeks ago... but the actual deployment hasn't happened yet--it's destined to begin next week.  In that case of course the collection might show up on this list--but it shouldn't be deleted--it has a future use.  But hopefully if your environment has a lot of collections and determining which ones might be safe to remove, this is a potential starting point.

Select c.collectionid, c.name [CollectionName]
from v_collection c
where
    c.collectionid not in (Select SourceCollectionID from vSMS_CollectionDependencies) -- include, excludes, or limited to
and c.collectionid not in (Select collectionid from v_deploymentsummary) -- any deployment, apps, advert, baseline, ts
and c.collectionid not in (Select Collectionid from v_ServiceWindow)
and c.collectionid not in (select collectionid from vClientSettingsAssignments)
and c.collectionid not in (select siteid from vSMS_CollectionVariable) -- OSD Collection Variables
and c.collectionid not in (Select a.ExcludedCollectionID from autoClientUpgradeConfigs a) -- ACU exclusion collection
and c.collectionid not in (select collectionid from v_collection where collectionid like 'sms%') -- exclude default collections

Another potential sql query for you to look for "collections not needed" could be this one.  What this one would be is it would sort, by "last time members changed in this collection".  The potential argument goes like this... even *if* that collection is being used for an active deployment... if the members of that machine based (not userbased) collection hasn't changed in years; how important is it to keep that particular deployment going / available?

;with cte as (select t2.CollectionName, t2.SiteID [CollectionID]
 ,(Cast(t1.EvaluationLength as float)/1000) as [EvalTime (seconds)]
 ,t1.LastRefreshTime, t1.MemberChanges, t1.LastMemberChangeTime,sc.SiteCode,
 case
  when c.refreshtype = 1 then 'Manual'
  when c.refreshtype = 2 then 'Scheduled'
  when c.refreshtype = 4 then 'Incremental'
  when c.refreshtype = 6 then 'Scheduled and Incremental'
 end as [TypeofRefresh]
,c.MemberCount,c.CollectionType
from dbo.collections_L t1 with (nolock)
join collections_g as t2 with (nolock) on t2.collectionid=t1.collectionid
join v_sc_SiteDefinition sc on sc.SiteNumber=t1.SiteNumber
join v_collection c on c.collectionid=t2.siteID
)
Select cte.collectionID, cte.CollectionName, CTE.[EvalTime (seconds)]
,Right(Convert(CHAR(8),DateADD(SECOND,CTE.[EvalTime (seconds)],0),108),5) [EvalTime (Minutes:Seconds)]
,cte.lastrefreshtime, cte.memberchanges, cte.lastmemberchangetime, cte.typeofrefresh, cte.membercount
from cte
where cte.collectiontype=2
and cte.collectionid not like 'SMS%'
order by lastmemberchangetime