Identifying Azure AD users with ConfigMgr

I wanted to share an interesting find I had with a customer today.

We were working on a task to populate some user collections for a deployment and we saw that one collection had a lot more users included than it should have. We figured it must be users coming from Azure AD (AAD) which made me wonder; how could we easily identify only AAD users and use that technique to exclude the unnecessary users from the collection we were creating?

First we tried using the “Agent Name” attribute on the user object. This could in theory work, since it lists the agents used for discovery.

SELECT DISTINCT sms_r_user.resourceid, 
                sms_r_user.resourcetype, 
                sms_r_user.NAME, 
                sms_r_user.uniqueusername, 
                sms_r_user.windowsntdomain 
FROM   sms_r_user 
WHERE  sms_r_user.agentname IN ( "sms_azuread_user_discovery_agent" )

We quickly found that the values of this property were listed as an array of strings and it eventually returned the same result as before. It turns out that WQL doesn’t support querying one exact value of an array. 🙁

We then we started thinking, there must be something that SCCM could grab from an AD user that doesn’t exist on AAD users.

Turns out there is!  On-prem AD users will always have the “DistinguishedName” (DN) attribute where AAD users will not.  AAD users will always have the “AADTenantID” where on-prem AD users will not.
User objects residing in both AD and AAD will fill both these properties with a value.

So, if the “DistinguishedName” value is null and the value of “AADTenantID” is not null, then it must be an exclusive AAD user, right? Exactly!

SELECT DISTINCT sms_r_user.resourceid, 
                sms_r_user.resourcetype, 
                sms_r_user.NAME, 
                sms_r_user.uniqueusername, 
                sms_r_user.windowsntdomain 
FROM   sms_r_user 
WHERE  sms_r_user.aadtenantid IS NOT NULL 
       AND sms_r_user.distinguishedname IS NULL

Only AAD users in one collection, works like a charm!

Keep in mind, the same holds true for exclusive AD users! Just flip the “is null” and “is not null” conditions like this:

SELECT DISTINCT sms_r_user.resourceid, 
                sms_r_user.resourcetype, 
                sms_r_user.NAME, 
                sms_r_user.uniqueusername, 
                sms_r_user.windowsntdomain 
FROM   sms_r_user 
WHERE  sms_r_user.aadtenantid IS NULL 
       AND sms_r_user.distinguishedname IS NOT NULL

I definitely learned something cool today and I hope you did too 😀