In this post I will show you how to query active directory security group members and export them to CSV(or excel) using PowerShell
While there are variety of ways available to export group membership to excel/CSV, the easiest method I found is using the combination of cmdlets in ActiveDirectory module & Export-CSV cmdlets. It helps you in exporting the member’s information to CSV file and it’s not much efforts to convert that CSV into EXCEL format if you insist that you need in XLS format only.
Since this code I am going to discuss uses ActiveDirectory module, make sure that you have at least one Domain Controller running ADWS service. Without that ActiveDirectory module will not work. ADWS is default with Domain Controllers running Windows Server 2008 R2 or above. For the lower versions, you need to install it manually. You can download ADWS for Windows Server 2003 and Windows Server 2008 from http://www.microsoft.com/en-sg/download/details.aspx?id=2852
Ok, now let jump the coding section.
First of all you need to import the ActiveDirectory module. This module is available on Windows 7 and Windows server 2008 or above only. If you have lower versions of operating systems, this is not available.
Import-Module ActiveDirectory
Once the module is imported, we can query the group members using Get-ADGroupMember cmdlet in this module
For example,
Get-ADGroupMember -Id "Group Name"
This will return list of objects that are part of the group. The objects include Users, computers and Group objects if any.
If you want to export this entire information to CSV, use Export-CSV cmdlet.
Get-ADGroupMember -Id "Group Name" | Export-CSV c:\temp\GroupOutput.CSV -NoTypeInformation
The above command is self-explanatory. I have used -NotypeInformation parameter for Export-CSV cmdlet to supress the type information of the objects it is exporting.
If you want to export group membership of multiple groups, then you have to try the below code.
Here the script reads the groups list from text file c:\temp\groups.txt and generates the output in c:\temp\GroupsInfo.CSV. The output will have the group name in first column.
$groups = Get-Content c:\temp\Groups.txt foreach($Group in $Groups) { Get-ADGroupMember -Id $Group | select @{Expression={$Group};Label="Group Name"},* | Export-CSV c:\temp\GroupsInfo.CSV -NoTypeInformation -Append }
Hope this helps and happy learning.
Comments on this entry are closed.
I tried adding multiple groups in groups.txt file and run the script, but output file is blank.
If anyone is wondering about multiple groups example it is missing -Append on the Export-Csv cmd
Thanks Roger for highlighting. I fixed it now.
The script works, but when i add append, i get the following error:
Export-Csv : A parameter cannot be found that matches parameter name ‘Append’.
-Append parameter is available from PowerShell v3.0. Please ensure you have this version to make -append parameter work.
Thanks for the script. How I get the list of groups in the text file?
What is the proper formatting for the Groups.txt file?
it is one group name per line.
Hi TechiBee,
Thanks for the script. Tested and runs OK
Have you got any script to remove multiple users from security group in AD?
HI
Will the append command line not work on powershell v1
Agree. I am surprised if anyone still using PowerShell v1 🙂
I was able to export localadmin group members with this command. Any idea how to use that csv to display LDAP decription fields and email addresses of the output for localadmin members csv?
Hi,
It needs script to be updated with additional logic. We will try to make a new article on that.
Thank you very much, it runs great.
How would you go about formatting the output? I’d like to view the group name and the usernames under it as columns.
Can you please write a script to export Nested group membership that will last for long run time like 10k to 100k input file should not matter to run the script!
Hi Jeevan, We are not providing scripts for custom requirements at the moment. If you can post what you tried and where you are stuck, we can help you with that.