Active Directory User Export Tool: CSV / JSON

Exporting users (or other objects) from Active Directory can be very handy.

PowerShell is the easiest way to do this, but a few options exist.

Most admins will automatically export data to the CSV format, but this is not always the best option.

If you need to use the exported data programmatically it’s often better to export it as JSON.

This article will cover both options. But first:

What Data to Export from Active Directory?

You can export users using the Get-ADUser command (available via RSAT).

If you want to export other types of objects, you can use Get-ADComputer, Get-ADGroup, or the generic Get-ADObject.

Apart from the type of object you also need to make a decision about the scope of objects and attributes:

What OU(s) do you limit the search to?
What additional filtering do you want?
What attributes/properties do you want to export?

The OU is designated by the SearchBase parameter, e.g.:

Get-ADUser -Filter * -SearchBase "OU=Alabama,OU=UnitedStates,OU=Users,OU=E365M,DC=Easy365Manager,DC=local"

Additional filtering is best performed using the very powerful LDAP filter, e.g.:

Get-ADUser -LDAPFilter "(department=marketing)"

By default, Get-ADUser will only return a very limited set of user properties.

Additional properties can be designated using the Properties switch and the Select-Object CmdLet, e.g.:

Get-ADUser -Filter * -Properties sAMAccountName,givenName,sn,userPrincipalName | Select-Object sAMAccountName,givenName,sn,userPrincipalName

All you need now is to combine the OU scoping, the LDAP filter, and the property selection; then, you have your query.

Next, you must select the type of export you want to make: CSV or JSON.

How to Export AD Users to CSV Using PowerShell

This format is mainly useful when the consumer is well-versed in Excel and, e.g., needs to present data to management.

One example is a project manager who wants you to provide an overview of mailbox migration progress that she can present to the CIO.

By providing the raw data in the CSV format, the project manager can easily use Excel formulas to summarize the data and create nice pie charts.

The easiest way to extract selected user properties into CSV would be something like this:

Get-ADUser -Filter * -Properties sAMAccountName,givenName,sn,userPrincipalName | Select-Object sAMAccountName,givenName,sn,userPrincipalName | Export-Csv c:\tmp\ADUsers.csv -NoTypeInformation

You should open the .csv file, split the data into columns, apply a filter and save it as .xlsx before sending it to the project manager:

How to Export AD Users to JSON Using PowerShell

If you need to access the exported data programmatically, it’s much better to export the data as JSON.

By saving the data to JSON, you can easily import the data again if needed.

One example is exporting some user properties for backup before making a scripted change.

The easiest way to extract selected user properties into JSON would be something like this:

Get-ADUser -Filter * -Properties sAMAccountName,givenName,sn,userPrincipalName | Select-Object sAMAccountName,givenName,sn,userPrincipalName | ConvertTo-Json -Compress | Out-File "c:\tmp\ADUsers.json"

The exported data is going to look similar to the following:

If you need a quick peek at the data (and assuming it’s not confidential), copy it to an online JSON-to-table converter:

The real fun with JSON exported data lies in the easy conversion back to scriptable objects!

Use the following PowerShell CmdLet to convert the data back into a user collection:

$Users = Get-Content "C:\tmp\ADUsers.json" | ConvertFrom-Json

You can then easily use the $Users variable to import data, export subsets of the data to CSV, and what not:

PS C:\> $Users | ? { $_.givenName -eq 'james' -and $_.sn -like 'm*' } | ft

sAMAccountName givenName sn       userPrincipalName
-------------- --------- --       -----------------
james.miller   James     Miller   james.miller@azure.skrubbeltrang.com
james.martinez James     Martinez james.martinez@azure.skrubbeltrang.com
james.moore    James     Moore    james.moore@azure.skrubbeltrang.com
james.martin   James     Martin   james.martin@azure.skrubbeltrang.com
james.mitchell James     Mitchell james.mitchell@azure.skrubbeltrang.com
james.morris   James     Morris   james.morris@azure.skrubbeltrang.com
james.morales  James     Morales  james.morales@azure.skrubbeltrang.com
james.murphy   James     Murphy   james.murphy@azure.skrubbeltrang.com
james.morgan   James     Morgan   james.morgan@azure.skrubbeltrang.com
james.mendoza  James     Mendoza  james.mendoza@azure.skrubbeltrang.com
james.myers    James     Myers    james.myers@azure.skrubbeltrang.com

Summary

With some planning, you can quickly provide powerful Active Directory data exports.

Consider the scope of your export in terms of OUs, LDAP filtering, and properties.

Be mindful if the exported data will be used for Excel reporting or further programmatic processing.