SCCM: How to exclude collections in CM07 queries.

In System Center 2012 Configuration Manager you can create “Exclude Collection” rules. This can be useful if you have a query based collection but want to exclude members of another collection.
Example: You have a collection (A) containing all computers with an application installed, then you need to create a new collection (B) containing all computers where the application is missing. To achieve this you can create a dynamic rule listing all computers in your environment and a second “Exclude Collection” rule excluding all members of the collection (A). As a result collection (B) will only contain clients where the application is missing.
If you for some reason still are using System Center Configuration Manager 2007 (and for some reason cannot migrate to CM2012), you can achieve the same by modifying your query.
Add the following to your Query (marked in red), J0100249 is the “Collection id”:

Select SMS_R_System.Name from  SMS_R_System where SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_J0100249)



But make sure you first add the clients in excluded colelction and then add above query