
close
close
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.
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.
advertisment
get-adgroup -filter * | sort name | select Name
I’ve sorted on the Name property and selected it.
Grabbing list of avaialble groups in domain by sorting on the name property in Windows PowerShell. (Image Credit: Jeff Hicks)
Get-adgroupmember "Chicago IT"
Nothing here is case-sensitive.
Using the Get-ADGroupMember cmdlet in Windows PowerShell to retrieve members. (Image Credit: Jeff Hicks)
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.
Sorting dynamically with Out-GridView. (Image Credit: Jeff Hicks)
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.
chi-engineering.csv file. (Image Credit: Jeff Hicks)
advertisment
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.
Modified chi-engineering.csv file. (Image Credit: Jeff Hicks)
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.
advertisment
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.
Selecting one or more groups to export. (Image Credit: Jeff Hicks)
Exporting groups to the CSV file. (Image Credit: Jeff Hicks)
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.
Error with using Get-ADGroupMember. (Image Credit: Jeff Hicks)
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.
More from Jeff Hicks
advertisment
Petri Newsletters
Whether it’s Security or Cloud Computing, we have the know-how for you. Sign up for our newsletters here.
advertisment
More in PowerShell
Microsoft’s New PowerShell Crescendo Tool Facilitates Native Command-Line Wraps
Mar 21, 2022 | Rabia Noureen
Most popular on petri
Log in to save content to your profile.
Article saved!
Access saved content from your profile page. View Saved
Join The Conversation
Create a free account today to participate in forum conversations, comment on posts and more.
Copyright ©2019 BWW Media Group