?
Solved

SCCM Collection Query

Posted on 2009-07-16
4
Medium Priority
?
1,353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The environment that this is running in is SCCM 2007 R2 running on a Windows 2008 R2 server. The PXE Distribution point is running on its own Windows 2008 R2 box. This is what Event viewer showed after trying to start the WDS service:  An erro…
Welcome to my series of short tips on migrations. Whilst based on Microsoft migrations the same principles can be applied to any type of migration. My first tip Migration Tip #1 – Source Server Health can be found listed in my profile here: http:…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

771 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