Sometimes you have the need to create a collection that is dynamically filled with members that are not part of another collection.

Last time I faced this scenario, was when configuring patch management. At the customer we created two collections. One named ‘Servers – Manual Restart’ and one collection named ‘Servers – Automatic Restart’.

We filled the ‘Servers – Manual Restart’ by hand creating a Direct Membership rule. After that we wanted a query to automatically fill the ‘Servers – Automatic Restart’ collections with servers that were not a member of the ‘Servers – Manual Restart’ collection.

The query below is what I used. In this case ‘P010001B’ is the collection ID of the ‘Servers – Manual Restart’ collection.

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.Client0 like '1' AND SMS_R_System.Obsolete0 like '0'
AND
SMS_R_System.ResourceId NOT IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE collectionid IN ('P010001B'))

As with any query you can specify one or more criteria to meet your own specific needs, as in the example above to narrow it further down if a client is installed and if the systems is obsolete or not.

Have fun!