ConfigMgr User Device Affinity (UDA) Collection Query

I was at the Midwest Management Summit at the Mall of America (#MMSMOA) this past week and met a ton of people.  Many of whom I have conversed with over Twitter and even more that I haven’t.  If you haven’t been to MMSMOA, I highly suggest you go next year (May 2018!).

At the end of one of the sessions, I engaged in a discussion about deploying software/updates to a Device Collection based on the systems Primary User.  Well, I promised I would post a blog so here it is.  To do this, we will leverage the User Device Affinity (UDA) functionality of ConfigMgr.

If you are unfamiliar with User Device Affinity, I suggest you read https://docs.microsoft.com/en-us/sccm/apps/deploy-use/link-users-and-devices-with-user-device-affinity first to familiarize yourself with the technology and how to configure it in your environment.

In order to create a collection based User Device Affinity relationships, you need to have two things:

  1. User Device Affinity assigned according to the link above (Either based on Client Settings or manual assignment)
  2. An Active Directory Security Group of user objects you wish to query against.  This group must be enabled in Active Directory Group Discovery located within Hierarchy Settings.

Warning: Be very careful when configuring your collection.  It’s possible for UDA to be enabled (either manually or via Client Settings) on both Clients and Servers.  Be sure to configure your Limiting Collection appropriately to ensure the end result contains ONLY the systems you expect.

select distinct
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 JOIN SMS_UserMachineRelationship ON
SMS_R_System.ResourceID=SMS_UserMachineRelationship.ResourceID
JOIN SMS_R_User ON
SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
WHERE SMS_UserMachineRelationship.Types=1 AND
SMS_UserMachineRelationship.IsActive=1 AND
SMS_R_User.UserGroupName="MYDOMAIN\\My-User-Security-Group"

There is a caveat to this whole process.  If using Client Settings to configure automatic discovery of Primary Users, you won’t catch everything right away.  This functionality is based on system usage over a period of time so it’s possible the relationships could fluctuate over time depending on how the system is used.

Too the people I spoke with at MMSMOA, and for everyone else, I hope you find this useful.  If anyone reading this has a more accurate query, please post in the comments and share the love.

 

 

Advertisements

Author: dhedges

I'm a Senior Client Systems Engineer specializing in OS Deployments and Automation using VBScript, PowerShell, MDT and SCCM. I enjoy working with technology and bending it to my will.

3 thoughts on “ConfigMgr User Device Affinity (UDA) Collection Query”

  1. Would it not make more sense to join on the resourceId rather than system name? At certain companies that manage mobile devices :-D, the system name is not unique and this query would not return accurate results.

  2. Would there be a way to leverage the query to use a user collection membership instead of an AD group membership? Trying to find to find info for a user collection based off a computer collection has been impossible. Computer collection based off a user collection has been easy to find, but I haven’t been able to reverse engineer the query, and this query looks like a much better start.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s