PowerShell Problem Solver: Exporting Active Directory Groups to CSV

Today’s PowerShell Problem Solver involves two common themes I see frequently: Active Directory groups and CSV files. The usual business need is to export members of a group to a CSV file so it can be opened in Microsoft Excel. Another need is to provide group membership information to a third party that doesn’t have the ability to query a group directly.

• Related: Create New Active Directory Users with Excel and PowerShell

As I will show you, using PowerShell it is not that difficult to solve this problem, although what I am going to show you will require the Microsoft Active Directory cmdlets on your desktop and at least one domain controller running the Active Directory web service. I’ll be using a Windows 8.1 desktop that has Remote Server Administration Tools (RSAT) installed and the Active Directory management tools enabled.
First, you may need to get a list of available groups in your domain.

​get-adgroup -filter *  | sort name | select Name

I’ve sorted on the Name property and selected it.

 
Once you know the exact group name, you can use the Get-ADGroupMember cmdlet to retrieve the members.

​Get-adgroupmember "Chicago IT"

Nothing here is case-sensitive.

 
You end up with a brief object for each member. Knowing this, let’s jump back to the previous command and enhance it.

​get-adgroup -filter * -properties Member| select Name,DistinguishedName,
GroupCategory,GroupScope,@{Name="Members";
Expression={( $_.members | Measure-Object).count}} |
Out-GridView

With this command, I am getting all groups and selecting a few properties. I am also creating a new property to show the total count of immediate group members. We’ll look at recursive searching in a moment. Using Out-Gridview makes it easy to dynamically sort simply by clicking on a column heading.

 
Note that some groups like Domain Users won’t get enumerated.
Let’s say I know I want to export the members of the Chicago Engineering group. I already know what the member objects look like, so I can select the properties I want to export with a simple one-line command.

​Get-ADGroupMember -Identity "Chicago Engineering" |
Select Name,SamAccountName,DistinguishedName |
Export-CSV -Path c:\work\chi-engineering.csv -NoTypeInformation

I am including the –NoTypeInformation parameter with the assumption that this CSV file will be used outside of PowerShell. If you need to change the delimiter, read the help for Export-CSV. If you plan on re-importing the CSV file later into a PowerShell session, then you should include the type information header. Otherwise, you can see I have a perfectly valid CSV file.

 
One limitation with Get-ADGroupMember is that you only see a small subset of account properties. But you may want to include some additional user information in your CSV file. This is a situation where PowerShell and the pipeline shine. We can take each member object and pipe it to Get-ADUser to retrieve the user account.

The output from Get-ADGroupMember can feed directly into Get-ADUser. For right now, I only want to get users who are immediate members of the group. I know that there is a nested group, which I want to skip for now.

​Get-ADGroupMember -Identity "Chicago Engineering" |
Where {$_.ObjectClass -eq 'user'} |
Get-ADUser -Properties Title,Department|
Select Name,Title,Department,SamAccountName,DistinguishedName |
Export-CSV -Path c:\work\chi-engineering.csv -NoTypeInformation

Remember that with Get-ADUser you need to specify properties you wish to see.

 
As I mentioned I know that this group has at least one nested group.

​Get-ADGroupMember -Identity "Chicago Engineering" | Group ObjectClass

You could write a PowerShell function to recursively get members from each nested group, but it is easier to simply use the –Recursive parameter. Here’s the previous command modified to now include all nested members.

​Get-ADGroupMember -Identity "Chicago Engineering" -Recursive |
Get-ADUser -Properties Title,Department |
Select Name,Title,Department,SamAccountName,DistinguishedName |
Export-CSV -Path c:\work\chi-engineering.csv -NoTypeInformation

I am only getting a few properties for each user, but you can be as detailed as you need to be. Just remember that CSV files work best with flat data so make sure any properties you request are simple values and not nested objects. You can still export, but you’ll need to use an XML format.

​Get-ADGroupMember -Identity "Chicago Engineering" -Recursive |
Get-ADUser -Properties * |
Export-CliXML -Path c:\work\chi-engineering-members.xml

Before I leave you to try all this out in your test domain, how about a little fun with this? Here is some code that uses Out-Gridview as an object picker. I am limiting my search to security groups.

​Get-ADGroup -filter "GroupCategory -eq 'Security'" –properties Member |
Select Name,@{Name="Members";
Expression={($_.member | Measure-Object).count}},
GroupCategory,GroupScope,Distinguishedname |
Out-GridView -Title "Select one or more groups to export" -OutputMode Multiple |
foreach {
  Write-Host "Exporting $($_.name)" -ForegroundColor cyan
  #replace spaces in name with a dash
  $name = $_.name -replace " ","-"
  $file = Join-Path -path "C:\work" -ChildPath "$name.csv"
  Get-ADGroupMember -identity $_.distinguishedname -Recursive |
  Get-ADUser -Properties Title,Department |
  Select Name,Title,Department,SamAccountName,DistinguishedName |
  Export-CSV -Path $file -NoTypeInformation
Get-Item -Path $file
}

Each group object is displayed in Out-Gridview. I’m including my code to show the member count but you can remove it as it will like take some time to process large groups.

 
Starting in PowerShell 3.0, you can select objects in Out-Gridview and pass them back to the pipeline. I can select multiple groups and click OK. Each group will then be exported to a CSV file, including a few user properties like Title and Department. The CSV file will be constructed using the group name, but I’m replacing spaces with dashes.

 
Finally, how about exporting all groups with a single PowerShell command?

​Get-ADGroup -filter "Groupcategory -eq 'Security' -AND GroupScope -ne 'DomainLocal' -AND Member -like '*'" |
foreach {
 Write-Host "Exporting $($_.name)" -ForegroundColor Cyan
 $name = $_.name -replace " ","-"
 $file = Join-Path -path "C:\work" -ChildPath "$name.csv"
 Get-ADGroupMember -Identity $_.distinguishedname -Recursive |
 Get-ADObject -Properties SamAccountname,Title,Department |
 Select Name,SamAccountName,Title,Department,DistinguishedName,ObjectClass |
 Export-Csv -Path $file -NoTypeInformation
}

With this command, I am filtering out domain local groups and those that have no members. For each group I’m getting the group members. Because some of the groups may have computer accounts, I can’t use Get-ADUser, at least not without errors. So I am using Get-ADObject.

The net effect is the same with the addition of the ObjectClass property, so I can later see which members are users and which are computers. The code creates a CSV file for each group.
Now, there is a potential problem you might encounter.

 
My “Test Group 1” has over 5000 members, which results in the “size limit for this request was exceeded” error message. If you think you might run into this problem, you can try retrieving the members with the group and exporting them.

​Get-ADGroup -filter "Groupcategory -eq 'Security' -AND GroupScope -ne 'DomainLocal' -AND Member -like '*'" -Properties Member |
foreach {
 Write-Host "Exporting $($_.name)" -ForegroundColor Cyan
 $name = $_.name -replace " ","-"
 $file = Join-Path -path "C:\work2" -ChildPath "$name.csv"
 $_.member | Get-ADObject -Properties SamAccountname,Title,Department |
 Select Name,SamAccountName,Title,Department,DistinguishedName,ObjectClass |
 Export-Csv -Path $file -NoTypeInformation
}

The downside is that this won’t handle nested groups. I will revisit this large group problem in a future article. But for now, you should be able to find one or more Active Directory groups and export the members to a CSV file. It really isn’t that difficult if you take your time and learn how to use the individual cmdlets.