Solved

SCCM Collection Query

Posted on 2009-07-16
4
1,336 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The question has been asked on multiple occasions as to how best to do printing in a remote desktop or terminal services environment.   It seems that this particular question has plagued several people and most especially as Terminal Services, as…
This is my 3rd article on SCCM in recent weeks, the 1st (http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/A_4466-A-beginners-guide-to-installing-SCCM2007-on-Windows-2008-R2-Server.html) dealing with installat…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now