How To Use PowerShell to Retrieve User Account MemberShip data and Export to CSV


Recently had a request to retrieve specific information out of Active Directory and save the results to a CSV file for our auditors. The data to retrieve for the user is Name,Enabled,Created and MemberOf. The catch was to remove the CN and OU from the memberof output from PowerShell. In this post we will cover just how to accomplish that.


The goal was to use PowerShell to retrieve the data from AD but also make it un-complicated and reusable. To do that I chose to use the Get-ADUser cmdlet and a bit of regex. In the code example below there are several pieces to keep it simple and reusable.

  • Create a Filter using Get-ADUser with all Properties and use SearchBase parameter to define where in AD to look. The reason for this is to allow for easily changing the search path in AD. Cal this variable $users.
  • Define a $file variable to set what we want to save the output to.
  • Create a foreach loop to iterate through every instance and output to the csv file in which we call Get-ADUser again using the variables we defined and add the regex.
  • Use regex expressions to remove CN and OU from memberof and comma seperate output for readability of the groups.

Here is the code.

The PowerShell script Queries a specific OU for Name, Created, Enable and MemberOf
and exports to a CSV file.
Queries AD for User account information and exports to a CSV file.
Change SearchBase Information for different OU.
Change file value for different filename. You must delete the old file if rerunning
As it appends to the file.

1.0 Initial release
$users = Get-ADUser -Filter * -Properties * -SearchBase “OU==Accounts,OU=MYOU,DC=DOMAIN,DC=COM”

$file = “ADUserInfo”
foreach ($user in $users) {

(Get-ADUser -Identity $user -Properties * | Select Name,Enabled,Created, @{N=’memberof’;E={[string]::join(“,”, ($_.memberof -replace ‘^CN=([^,]+),OU=.+$’,’$1′))}} | Export-CSV -path c:\Support\$file.csv -append -NoTypeInformation)

The real beauty of the code is the regex on the memberof output. To achieve comma separation  you must use the [string]::join and put the regex within the expression.


The objective was met and the PowerShell code written is easily reused for different OU’s and save as different file names.

Leave a Reply

Your email address will not be published. Required fields are marked *