Updated on :  03/02/2015
Relevant to:   SCCM 2007, SCCM 2012 (including R2 and R3 versions)

---

 

Probably the thing that gives SCCM most of its power (IMO) is the ability to target programs at machines with very specific properties, by using query based collections – however this is also something that we get constant emails about from our support customers.

Query based collections allow an administrator to provide any criteria that the SCCM database may hold about systems, and automatically make those systems a member of that collection.

For example, you may want to update to Acrobat Reader 9.3 on all your machines, however out in the field at the moment you have a mix of Acrobat Reader 7, 8.1, 9.2 etc. and the install methods for each of these is different (this is NOT the case, I’m just trying to present a hypothetical example!)

Within your package, you would create an upgrade program for each possible situation, and then advertise each of those programs to a different collections – each of which we will plug in a query to in order to identify the correct clients.

---

 

General process

• In order to create a collection, simply go to
  • SCCM 2007 – Computer Management | Collections
  • SCCM 2012 – Assets and Compliance | Device (or user) collections
• Right click and use the context menu to create a new collection
• Give the collection a name and define a limiting collection
• 
• Membership rules
  • SCCM 2007 – You will be presented with the “Membership rules” screen  where you can click the Database icon, to create a new query rule
  • SCCM 2012 – Click on “Add rule – Query Rule”
  • ,
• Give the query a name and then select “Edit query statement”
• Select the “Criteria” tab
• Select the “new criteria” button
• 
• From this screen, you can enter the values in the examples below to get the desired collection membership

---

 

Ensuring SCCM is collecting the information you want to search on

If you wish to query based on properties such as AD group membership, OU name or file versions, you need to make sure you have configured SCCM to collect that information.

Configuring AD discovery can be configured from – Administration | Hierarchy Configuration | Discovery methods

Configuring software files discovery can achieved via – Administration | Client settings | software inventory

 

Examples of common SCCM collection queries

Collection that identifies computers in a specific group:

Criterion Value:               Simple

Where:                             System Resource – System Group Name

Operator:                         is equal to

Value:                              <Group Name>

Collection that identifies computers in a specific Active Directory OU

Criterion Value:                Simple

Where:                              System Resource – System OU Name

Operator:                          is equal to

Value:                               <OU Name>

Collection that identifies computers in a specific Active Directory Site

Criterion Value:                Simple

Where:                              System Resource – AD Site Name

Operator:                          is equal to

Value:                               <AD Site Name>

Collection that identifies computers with a specific program in Add/Remove Programs

Criterion Value:                Simple

Where:                              Installed Applications – Display Name

Operator:                          is equal to

Value:                               <Program Name>

Collection that identifies computers with a specific file

Criterion Value:                Simple

Where:                             Software Files – File Name

Operator:                          is equal to

Value:                               <File Name>

Collection that identifies computers of a specific hardware model

Criterion Value:                Simple

Where:                              Computer System – Model

Operator:                          is equal to

Value:                               <Model>

---

 

 

Building more complex queries

So the above examples give very basic example of how to create queries… the next step is starting to build more complex queries

Queries using and/or

Collection that identifies computers with a specific file, in a specific path of a specific version

Select “new”, then

Criterion Value: Simple, Where: Software Files – File Name, Operator: is equal to, Value:<File Name>

Then Select “new” again

Criterion Value: Simple, Where: Software Files – File Path, Operator: is equal to, Value:<File Path>

Then Select “new” again

Criterion Value: Simple, Where: Software Files – File Version, Operator: is equal to, Value:<File Version>

You will notice that each time the criteria is added with an “and” statement in-between. It should be pretty clear what this means.

These can be changed by highlighting the “and” statement and clicking the “&|” button to toggle the “and” statement to an “or” statement

 

Using brackets

We can also bracket statements, which allows us further power, because we can then use queries like Statement1 and (statement 2 or Statement3). To use brackets, simply select the two (or more) statements you want to bracket and select the “()” button

 

Using Not

A “not” statement is pretty self-explanatory, and can be used by clicking on the “!” button. An example of this could be show all computers that have an entry for C:\Program Files\Microsoft Office\Office15\excel.exe that isn’t file version 15.0.4621.1000 (excel 2013)

 

Using In

The “In” statement can be used along with the NOT statement in order to exclude specific items from the collection (Generally not used in SCCM 2012 – see include/exclude collections in the next section)

Examples

Include all computers in Site1 and Site2:                                       Select * from SMS_R_System Where ADSiteName In (“Site1”,”Site2”)

Include all computers except for those in Site1 and Site2 :            Select * from SMS_R_System Where ADSiteName Not In (“Site1”,”Site2”)

 

SCCM 2012 – using include and exclude collections

SCCM 2012 has built-in include and exclude collections, meaning that queries (compared to SCCM 2007) in some cases become substantially easier.

These new options allow admins to quickly build new collections based on including or excluding other collections – which can be quite powerful.

 

---

 

Feeding DCM baselines into collections, so you automatically remediate non-compliant machines

So for some reason, one that doesn’t make sense to most admins, the ability to create a collection directly from a DCM  baseline isn’t built into SCCM 2007.  Fortunately, there is a TechNet article on how to perform this function http://technet.microsoft.com/en-us/library/bb680546.aspx

---

 

 

I hope this article has given you a start to understanding how SCCM collections can be used to target specific machines in your environment.

 

---