Was working on a crazy collection query today for a client and I needed to exclude the members of another collection in addition to the other queries I had already built into the collection.  Took me awhile of searching, but I finally found this post on TechNet.

This is the 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.Client = 1 and

SMS_R_System.ClientType = 1 and SMS_R_System.ResourceId not in

(select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

 

Replace the red XXXxxxxx with the CollectionID you want to use.

Category: infrastructure