Creating Device Collections Based on Primary Users (and vice versa)

Go beyond the limitations of the SCCM query builder wizard and create intelligent collections using custom WQL.

The Problem

It's pretty simple and straightforward to build a device collection based on combinations of other device collections. It is also doesn't take much to teach someone how to use the GUI query builder to create a device collection filtered on one of the many hardware inventory fields, such as OS version, or devices with a specific software GUID installed.

But what if you want to create a device collection of the primary devices of a specific group of users? Where's the option in the GUI query builder for that? Anybody? Andybody?... Ok, that was a trick question. You can't do it. When you are creating a device collection, you're not even offered the option to select from or join to user collections.

Only system resource classes are available

"But," you say, "doesn't SCCM already have all the data it needs in the SMS Provider classes?"

You're really sharp! It sure does. We have classes defining our computers. We have classes defining our users. We have classes defining the relationships between our computers and our users. We even have classes defining the membership of all the other existing collections in SCCM.

It seems like we should be able to combine this data in a way that produces the device collection we want. And we can - we'll just have to bypass the limited query builder. As they say, if you want something done right, you have to script it yourself. (Or something like that.)

First, Some Key Concepts

I know you're just dying to dive into the WQL at this point, but let's quickly cover a couple of ConfigMgr concepts that are important to understand. Feel free to skip this if you don't need the crash course.

Linking Users to Computers

There are 2 main ways that SCCM identifies usage relationships between users and computers: Metering of Console Usage and User Device Affinity (UDA).

Metering Console Usage

By reading the logon/logoff events from the Windows Event Log, the SCCM client tracks all of the user accounts that login to a given computer, the number of logons per user account, as well as the total amount of time that each user has been logged on to that computer. This data is summarized and then returned to SCCM via hardware inventory collection cycles. Based on the usage summaries, the SCCM client also calculates the single user who has been the most frequent user of the the computer (based on total console usage time). This user is identified as the Top Console User (TCU), and the TCU is tracked via hardware inventory for the computer object.

User Device Affinity

ConfigMgr also incorporates a concept called User Device Affinity. This is an explicit user-device relationship that assigns a "primary" status. The user is a "primary user" of the computer, and the computer is a "primary device" of the user. Unlike metering console usage for a TCU, UDA relationships are not exclusive; one user may have multiple primary devices (if your environment is configured to allow this), and a single computer may have multiple primary users.

UDA relationships can be defined/created in various ways:

  • An SCCM administrator can manually add/remove UDA relationships via the ConfigMgr console.
  • An SCCM administrator can use the ConfigMgr console to define rules where UDA relationships are automatically created according to given criteria based on the metered console usage data in hardware inventory. For instance, any user who is logged on to a given computer for at least 30 hours during any consecutive 14 day stretch automatically becomes a primary user.
  • If allowed by policy, a user can manually set her current device as a primary device via the Application Catalog website. (ConfigMgr dev request: Why can't we do this through Software Center yet?!)

If you have your environment configured to automatically assign UDA relationships based on metered usage, then the TCU data and UDA data should be quite similar. But there will be some differences based on how you configure things, so understanding the differences between these two classifications is important to ensure you properly understand the results you'll get from the query you build.

SMS Provider Classes

The SMS Provider creates classes for both the console usage and user device affinity. The SMS_G_System_SYSTEM_CONSOLE_USAGE class contains the TopConsoleUser property. And the SMS_UserMachineRelationship class has instances for each UDA relationship in your environment.

Database Joins

For you to understand what these queries are doing, it's important for you to be familiar with the concept of joining one dataset to another. So, if you're not already familiar, take a few minutes to go through this SQL Joins tutorial.

Let's Dive In

I promise that I will eventually provide you with some actual WQL queries, but before I do, I want to explain the concept behind these queries so that you can adapt them for your own needs.

The General Idea

First, we need to have a user query that returns only the users that we're interested in. Then, we'll build our device query like this:

  1. We start with the full set of computer objects.
  2. We join that data to our usage data, which contains the usernames associated with each device. This may be either TCU data or UDA data.
  3. Finally, we use the WHERE filter and IN operator to look for those usernames in the results of the user query that we previously defined. In WQL/SQL parlance, this is called a subselect or nested select.

A Detailed Example

Let's build a device collection that finds devices where the Top Console User is a member of an existing user collection in SCCM.

Following the formula I laid out above, our first step is to construct the user query that returns only those users in the collection we specify. We'll need the Collection ID for the target user collection. You can get this from the SCCM console. For this example, let's assume the user collection ID is 'ABC00001'. In that case, the membership of that collection will be found in the SMS_CM_RES_COLL_ABC00001 class. So, to return a list of unique usernames for users in that collection, we can use this query:

SELECT SMS_R_User.UniqueUserName 
FROM SMS_R_User
  INNER JOIN SMS_CM_RES_COLL_ABC00001 ON
    SMS_CM_RES_COLL_ABC00001.ResourceID = SMS_R_User.ResourceID

Next, we start building the device query by joining our set of computer resources to the usage dataset. This query will give us a dataset of all computers with their Top Console User:

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,
  SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser

FROM SMS_R_System
  LEFT JOIN SMS_G_System_SYSTEM_CONSOLE_USAGE ON 
    SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId

The last step is to filter the device dataset by searching for usernames in the results of our user query. We combine the two queries above, and the resulting complete WQL query is:

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,
  SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser

FROM SMS_R_System
  LEFT JOIN SMS_G_System_SYSTEM_CONSOLE_USAGE ON 
    SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser IN
  (SELECT SMS_R_User.UniqueUserName 
   FROM SMS_R_User
     INNER JOIN SMS_CM_RES_COLL_ABC00001 ON
       SMS_CM_RES_COLL_ABC00001.ResourceID = SMS_R_User.ResourceID)

And that's it. Using this formula, you can tweak the specifics to accomplish whatever you need.

Various Examples

To demonstrate some other possible scenarios, I'm going to include a few other completed sample WQL queries to help get you started.

Devices where the UDA Primary User is a member of an SCCM user collection with ID:ABC00001

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,
  SMS_UserMachineRelationship.UniqueUserName

FROM SMS_R_System
  LEFT JOIN SMS_UserMachineRelationship ON 
    SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_UserMachineRelationship.UniqueUserName IN
  (SELECT SMS_R_User.UniqueUserName 
   FROM SMS_R_User
     INNER JOIN SMS_CM_RES_COLL_ABC00001 ON
       SMS_CM_RES_COLL_ABC00001.ResourceID = SMS_R_User.ResourceID)

Devices where the UDA Primary User is a member of an AD Security Group named "TestUsers" in the Contoso domain

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,
  SMS_UserMachineRelationship.UniqueUserName

FROM SMS_R_System
  LEFT JOIN SMS_UserMachineRelationship ON 
    SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_UserMachineRelationship.UniqueUserName IN
  (SELECT SMS_R_User.UniqueUserName 
   FROM SMS_R_User
   WHERE SMS_R_User.SecurityGroupName = 'Contoso\TestUsers')

Devices where the Top Console User is a member of an AD Organizational Unit named "Users_HR"

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,
  SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser

FROM SMS_R_System
  LEFT JOIN SMS_G_System_SYSTEM_CONSOLE_USAGE ON 
    SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser IN
  (SELECT SMS_R_User.UniqueUserName 
   FROM SMS_R_User
   WHERE SMS_R_User.DistinguishedName like '%OU=Users_HR%')

Creating the New Collection

Now that you've got your custom WQL query, you can use it to define a new collection membership rule.

First, add a new membership rule of type Query Rule:
Create a new Query Rule

Next, choose Edit Query Statement:
Edit Query Statement

In the query builder window, choose Show Query Language:
Show Query Language

And finally, paste in your WQL query and click OK:
Paste in Custom WQL

Extra Credit

The same concepts can also be used to create a collection of primary users, based on a known collection of computers. In short, your nested select would contain the device query, and the top level select would be against SMS_R_User. Here's one example:

Users who are Top Console Users of Devices in the SCCM Device Collection ID:ABC00002

SELECT 
  SMS_R_USER.ResourceID,
  SMS_R_USER.ResourceType,
  SMS_R_USER.Name,
  SMS_R_USER.UniqueUserName,
  SMS_R_USER.WindowsNTDomain,
  SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID

FROM SMS_R_User
  LEFT JOIN SMS_G_System_SYSTEM_CONSOLE_USAGE ON 
    SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser = SMS_R_User.UniqueUserName

WHERE SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceID IN
  (SELECT SMS_R_System.ResourceID
   FROM SMS_R_System
     INNER JOIN SMS_CM_RES_COLL_ABC00002 ON
       SMS_CM_RES_COLL_ABC00002.ResourceID = SMS_R_System.ResourceID)

I hope this was helpful. If you have any comments or questions, or if you have an idea about how to further improve this approach, you can connect with me via the comments below or via Twitter.

Show Comments