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.