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

May 14, 2016 Posted by newlife007 on May 14, 2016 in Powershell, Windows | 0 comments

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

Introduction

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.

Process

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.

<#
.SYNOPSIS
The PowerShell script Queries a specific OU for Name, Created, Enable and MemberOf
and exports to a CSV file.
.DESCRIPTION
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.
.EXAMPLE
./adproperties1.ps1

Changelog:
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.