Reporting on synchronized user’s OU via the Graph SDK for PowerShell

For this week updated script sample, we’re tackling a very simple scenario – use the cloud PowerShell cmdlets to provide a report of all synced users, along with information of which on-premises OU they are homed in. As a reminder, there are no OUs in Microsoft 365/Azure AD, and the closest “replacement” would be administrative units. For hybrid identity scenarios however, information about the on-premises object properties is exposed as part of the cloud object’s properties, namely the set of onPremises* attributes:

With that info out of the way, constructing this script is a walk in the park. All we need to do is connect via the Graph SDK for PowerShell and fetch the list of all synced users, then parse their onPremisesDistinguishedName attribute value. An alternative approach would be to just filter any user objects with non-null values for onPremisesDistinguishedName, however it looks like this property is not currently supported for filtering. The joys of using the Graph…

As with all things Graph, you do need permissions to run the script. In this case, User.Read.All should suffice, and this is what the script will check for/insist on adding. If you have been using the Graph SDK for any sort of reporting, chances are you already have said scope consented do, and likely some broader ones, such as Directory.Read.All.

After connecting, we gather the list of all synced users via:

Get-MgUser -All -Filter "onPremisesSyncEnabled eq true" -Property displayName,userPrincipalName,onPremisesDistinguishedName | select displayName,userPrincipalName,onPremisesDistinguishedName

In case you want to enrich the output generated by the script with additional properties, this line (line 16) would be where you should add them, in both the –Property parameter value and select statement.

Apart from getting the list of users, the only other thing the script does is to parse the value of the onPremisesDistinguishedName property, as follows:

$userOU = $user.onPremisesDistinguishedName -replace '^(?:.+?(?<!\\),){1}(.+)$', '$1'

This handy regex, provided by Alexander in his (very constructive) critique of one of my previous script samples, takes care of extracting the OU part, including ones containing the comma character as part of the user’s name. Then, it’s output time, and by default the script will generate a CSV file in the current directory, sorted by the OU value. Here’s how the output will look like:

And that’s it. Get the script from my GitHub repo and let me know if you run into any issues with it. Because it’s a real simple one, there are no parameters for you to leverage, so you can run it directly:

.\OU_reportV2.ps1

One last thing to add, I’ve tested the script with both the “legacy” and V2 versions of the Graph SDK, but you might need to make some small changes depending on how you run it. And of course, if you want a fully automated solution, replace the connectivity block with code that leverages client secret/certificate or managed identity.