Some people may already know about this but I ran into it for the first time and thought it was interesting. We needed to create a collection in SMS that excluded all resources that were in a specific group in AD. Well, the obvious way to pull it off didn't work.
Anyway, here's what I had to do:
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 Name Not In (select distinct Name from SMS_R_System where SystemGroupName = "DS\\G113-KeepMSNetwareClient")
Instead of:
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 SystemGroupName Is Not Like "DS\\G113-KeepMSNetwareClient")
Basically, you have to have a subquery that returns members of the group and then do a "Not In" on that instead of just trying a "Is Not Like groupname" or "Not = groupname" type exclusion. From what I was reading this isn't unique to groups. You have to do it for a lot of inventory info.
Also, I found that the nested query (the part in the parenthesis in the 1st example) had to only return “Name“. I tried nesting that query with all of the normal stuff that gets returned (ResourceType, SMSUniqueIdentifier, etc.) but it didn't work then either.