Solved

SCCM Collection Query

Posted on 2009-07-16
4
1,345 Views
Last Modified: 2013-11-21
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
0
Comment
Question by:oldhammbc
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:JonLambert
ID: 24876391
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
 
LVL 8

Author Comment

by:oldhammbc
ID: 24876655
I tried your above statement but that brings back no results at all.  I should be getting about 250-300 pc's.
0
 
LVL 10

Accepted Solution

by:
JonLambert earned 500 total points
ID: 24883791
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
 
LVL 8

Author Closing Comment

by:oldhammbc
ID: 31605363
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Case Summary: In this Article we introduce the new method to configure the default user profile using Automated profile copy with sysprep rather than the old ways such as the manual copy of a configured profile to default user profile Old meth…
Remote Desktop Protocol or RDP has become an essential tool in many offices. This article will show you how to set up an external IP to point directly to an RDP session. There are many reasons why this is beneficial but perhaps the top reason is con…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question