Insights Creating a SCCM Device Collection Based on User Properties

Creating a SCCM Device Collection Based on User Properties

In a previous post, I covered how create a collection without a Limiting Collection.  Now it’s time to talk about why you would want to do that.

I have a customer that has a lot of processes built on organizing users with Active Directory properties.  With User and Device Affinity in SCCM, this seems like a great way to leverage that information to report on devices based on properties of user.  If you are writing your own SQL reports, you can use the v_UserMachineRelation view to link devices and users, but what if you want to use the built-in reports for Asset Intelligence?  All of these reports have a built-in parameter for collections, so you need to create collections of devices based on user properties, which is easier said then done.

There are two problems that you need to overcome for these collections, User and Device Affinity data is not store in WMI and Limiting Collections block access to user or device data, based on the type of collections.

To start, since User and Device Affinity isn’t part of WMI, so we need to find a value to tie users to devices.  WMI does contain SMS_G_system_SYSTEM_CONSOLE_USAGE.TopConsoleUser, which is the user who has been logged in the most on the device.  Since time logged into a device is the metric User and Device Affinity relies on, it should match your Primary User for a device, so you can use it to associate a user with a device in a WMI query.  However, if you have manually defined relationships, you will need to manually manage collections.

Next is the issue of accessing user data from a device collection.  This problem boils down to how the Limiting Collection for a device collection works.  If your collection uses All Systems as a limiting collection, then it will query all devices for membership rules, but it will not reference any users or user properties to evaluate membership.  The solution is to remove the Limiting Collection, which I covered here.

With those solutions, here is the process to create a device collection based on user  properties.

  • Create a query to select devices based on user properties using SMS_G_system_SYSTEM_CONSOLE_USAGE.TopConsoleUser to join them.
    • Be sure to select the “Not collection limited” option when creating the query.
  • Create a device collection.
    • Use All Systems as the Limiting Collection.
    • Import your query for the membership rules.
    • The collection will not contain any members at this point.
  • Remove the Limiting Collection as described here.
  • Run a Update Membership on the collection.
    • This will populate the collection based on your query and make it read only, so be sure your query is correct.

From here, your collection is ready to use with Asset Intelligence or any other collection base reports.