Unused Collections

select
 c.SiteID CollectionID
, c.CollectionName
, c.CollectionComment
, case when c.CollectionType = 1 then 'User' when c.CollectionType = 2 then 'Device' end CollectionType
, c.MemberCount
, c.CollectionVariablesCount
from vCollections c
left join v_CollectionRuleDirect crd on crd.CollectionID = c.SiteID
left join v_CollectionRuleQuery crq on crq.CollectionID = c.SiteID
left join v_Advertisement a on a.CollectionID = c.SiteID
left join v_DeploymentSummary ds on ds.CollectionID = c.SiteID
left join Collection_Rules tar on tar.ReferencedCollectionID = c.SiteID
left join Collection_Rules con on con.CollectionID = c.CollectionID
left join v_ServiceWindow sw ON sw.CollectionID = c.SiteID
left join vSMS_DPGroupCollections dp on dp.CollectionID = c.SiteID
where
 crd.CollectionID is null -- No direct membership rules
and crq.CollectionID is null -- no query rules
and a.CollectionID is null -- no advertisements
and ds.CollectionID is null -- no deployments
and tar.ReferencedCollectionID is null -- not referenced in another collection
and con.CollectionID is null -- not referencing another collection
and sw.CollectionID is null -- no service windows
and c.PowerConfigsCount = 0 -- no power management
and c.CollectionVariablesCount = 0 -- no collection variables
and c.MemberCount = 0 -- just to ensure there are no members
and dp.CollectionID is null -- not associated with any distribution point groups
and c.Flags & 0x01000000 <> 0x01000000 -- not set to show in endpoint protection dashboard
and c.SiteID not in (select LimitToCollectionID from v_Collections where LimitToCollectionID is not null) -- not used as a limiter
order by c.CollectionType desc, c.SiteID
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s