powershell - How to Export-CSV of Active Directory Objects?

Question

I'm trying to get a dump of all user records and their associated groups for a user ID revalidation effort. My security officer wants it in CSV format.

This works great:

Get-ADUser -Filter * -Properties * | Select-Object -Property Name,SamAccountName,Description,EmailAddress,LastLogonDate,Manager,Title,Department,whenCreated,Enabled,Organization | Sort-Object -Property Name | ConvertTo-CSV

However, that does not include the groups the user is a member of.

Attempts at something like this have failed:

Get-ADUser -Filter * -Properties * | Select-Object -Property Name,SamAccountName,Description,EmailAddress,LastLogonDate,Manager,Title,Department,whenCreated,Enabled,Organization, @{$_.MemberOf |Get-Group|ForEach-Object {$_.Name}} | Sort-Object -Property Name | ConvertTo-CSV

This also failed:

Get-ADUser -Filter * -Properties * | Sort-Object -Property Name | ForEach-Object {
    $_ | Format-List -Property Name,SamAccountName,Description,EmailAddress,LastLogonDate,Manager,Title,Department,whenCreated,Enabled
    $_.MemberOf | Get-ADGroup | ForEach-Object {$_.Name} | Sort-Object
}  | ConvertTo-CSV

I'm probably missing something simple. Any help would be greatly appreciated. Thanks!

Answer 1

From a Windows Server OS execute the following command for a dump of the entire Active Director:

csvde -f test.csv

This command is very broad and will give you more than necessary information. To constrain the records to only user records, you would instead want:

csvde -f test.csv -r objectClass=user 

You can further restrict the command to give you only the fields you need relevant to the search requested such as:

csvde -f test.csv -r objectClass=user -l DN, sAMAccountName, department, memberOf

If you have an Exchange server and each user associated with a live person has a mailbox (as opposed to generic accounts for kiosk / lab workstations) you can use mailNickname in place of sAMAccountName.