SCCM Configmgr SQL WQL query compare 2 or more collections to get the difference

This is quick post to show you ,how to compare 2 or more collections to find clients that are not member of other collections. The reason for me to write this collection is ,for server patching ,we have been using direct membership rules ( I know AD sec groups is good way to automate this but lets leave this for now ) due to multiple business units with different maintenance windows .

There could be multiple scenarios to compare 1 collection with another collection for application deployment ,OSD etc.

So i want to compare the list of servers that are in Active directory are part of the patching collections or not . I am writing up another blog post on how to manage software updates for workstations or servers in an effective manner to achieve good compliance rate with some nice dashboard reports.

This way ,i can get to know the servers in AD that are supposed to patch on monthly basis are missing or not in patching collection. You can also achieve this using SQL query which is also listed in this blog post.

So i created a collection based on Active directory OU with collection ID: PS100318  .Creating collection with OU filter is straight forward.

I have another parent collection that is used for patching PS100315 .This collection include lot of individual collections with its own maintenance window set for patching.

Now ,i need to compare the OU based collection (PS100318 ) to find out if any server not in patching collection (PS1000315).

Collection Query (sub selected query):

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_FullCollectionMembership   where CollectionID = "PS100318") and SMS_R_System.ResourceId not in (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE collectionid IN ('PS100315'))

If you have more than 1 collection to compare ,simply add all your collections into IN condition i.e WHERE collectionid IN ('PS100315',’PS100316’,’PS1000317’)) 

You can also use include exclude collection mechanism to do the same. Thanks to Nash for pointing this out.

SQL Query:

select fcm.name
from v_FullCollectionMembership fcm
where fcm.CollectionID='PS100318 '
and fcm.name not in (select fcm1.name from v_FullCollectionMembership fcm1 where fcm1.CollectionID='PS100315')

you can expand SQL Query further to know its OS,hardware inventory ,client installed etc.

Hope it helps!