Query: Build collection to exclude members of another collection

This has proven to be quite useful. Here's how to create a query-based collection that will exclude members of another collection:

1. Create a collection that will contain all clients to be excluded from another collection (we'll call it 'ExclusionsCollection' for this example.)

2. Once you have that, go into the Collections table in the SMS SQL database and find the row that contains 'ExclusionsCollection'. Note the cooresponding 'ResultTableName'.

3. In the collection that you want to exclude the members of 'ExclusionsList' from, copy and paste the following 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 Client = 1 and ClientType = 1 and
ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

4. Replace the item above in red with your ResultTableName.

So, machine will be manually entered into 'ExclusionsCollection' will be excluded from the collection with the above query. So say that you wanted to deploy a package to all XP machines except for a specific few, you'd create a collection and limit it to the 'All XP Machines' collection, and then add the above query.