[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SCCM Collection Query

Posted on 2009-07-16
4
Medium Priority
?
1,356 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 here: http://www.experts-exchang…
Know what services you can and cannot, should and should not combine on your server.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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