SCCM Collection Query

HI,

I am trying to create a query on a collection in SCCM but am having problems getting it to show me the correct data.
I have 2 groups, Group1 and Group2.  The pc's that are in Group2 are also in Group1.

I need the collection to show me PC's that are in domain\group1 but that are not in domain\group2 and also need it to not include pc12345 and pc67890 (which will be members of group1)

Can anyone offer me some advice on the structure of the query, I currently have:-

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.SystemGroupName = "DOMAIN\\Group1" and SMS_R_System.SystemGroupName != "DOMAIN\\Group2" and SMS_R_System.Name != "PC12345" and SMS_R_System.Name = "PC67890"

But that doesnt give me the correct information, its just lists pc67890

Thanks
LVL 8
oldhammbcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JonLambertCommented:
The trick is to use a second select statement:  I haven't verified the statement below (straight write), but the principle is what I've used before for the exact purpose that you've stated.

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.SystemGroupName = "DOMAIN\\Group1" and SMS_R_System.ResourceID not in (Select SMS_R_System.ResourceID from SMS_R_System Where SMS_R_System.SystemGroupName  = "DOMAIN\\Group2") and SMS_R_System.ResourceID not in (Select SMS_R_System.ResourceID from SMS_R_System Where SMS_R_System.Name = "PC12345" OR SMS_R_System.Name = "PC67890")
0
oldhammbcAuthor Commented:
I tried your above statement but that brings back no results at all.  I should be getting about 250-300 pc's.
0
JonLambertCommented:
OK .. i've now got one I've tested :)

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.SystemGroupName = "D\\ComputerGroup1" and SMS_R_System.ResourceId not in (Select ResourceID from SMS_R_System where SMS_R_System.SystemGroupName = "D\\ComputerGroup2") and SMS_R_System.SystemGroupName not in ("PC67890")

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oldhammbcAuthor Commented:
Thanks very much for this, worked great, only had to change 1 thing, you had accidently put systemgroupname in the last line instead of systemname.  MY final code was:-

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.SystemGroupName = "D\\ComputerGroup1" and SMS_R_System.ResourceId not in (Select ResourceID from SMS_R_System where SMS_R_System.SystemGroupName = "D\\ComputerGroup2") and SMS_R_System.SystemName not in ("PC67890")

Thanks again for your help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.