How to Extract Group Members from Active Directory and Export It to CSV file?

1.3K

In this article, we’ll take a look at few examples of using PowerShell to extract group members from different Active Directory group. This article should teach you how to build a list of accounts in a specific Active Directory group and export it to a CSV file, which is convenient to process in MS Excel and other Office programs.

Tools to list ad group members

Previously, to build a list of users in Active Directory group, you had to use VBS scripts, or DSQuery or CSVDE command-line utilities, which were not flexible enough and convenient.

To interact with Active Directory from PowerShell, Microsoft developed a special module — PowerShell Active Directory module. For the first time this module appeared in Windows Server 2008 R2 and in order to use it you must load it into your PowerShell session at first:

Import-Module ActiveDirectory

In Windows Server 2012/R2/Windows Server 2016, this module is automatically installed and loaded when the ADDS (Active Directory Domain Services) role is installed on the server when the server is promoted to a domain controller.

In desktop operating systems (Windows 10/Windows 7), the Active Directory Module for Windows PowerShell is included in the Remote Server Administration Tools, which you need to download, install and enable separately.

Please note that in order to use the ActiveDirectory module, you don’t need to be a member of the Domain Admins group, any authenticated domain user can obtain information about Active Directory users and groups using AD PowerShell module.

Using the Get-ADGroupMember cmdlet

To get the information about AD group membership, use the Get-ADGroupMember cmdlet.

For example, to display the list of members of the Domain Admins group, run the following command:

Get-ADGroupMember ‘Domain Admins’

Displaying the full list of groups in Active Directory

If you do not know the exact group name, you can display the full list of groups in Active Directory using the command:

Get-ADGgroup -filter * | sort name | select Name

You can display only usernames:

Get-ADGroupMember -Identity ‘Domain Admins’| ft name

Getting nested groups memberships

If the specified group contains other Active Directory groups, you must use the Recursive parameter to list members of Active Directory based on nested groups.

Get-ADGroupMember -Identity ‘Domain Admins’ -Recursive | ft name

The -recursive switch instructs the Get-ADGroupMember command to parse each nested group and display only objects that are not containers (user or computer). This command will display even those users who do not directly belong to the specified group.

Getting detailed Active Directory group information

You can display more detailed information about Active Directory group members:

Get-ADGroupMember -Identity ‘Domain Admins’ | foreach { Get-ADUser $_ -Properties * }

You can use the more complex PowerShell command, which allows you to list all members of a certain domain security group with information about the company, department and job title, followed by sorting and grouping depending on the specific attribute (company):

Get-ADGroupMember -Recursive ‘Domain Admins’ | ForEach {

Get-ADUser -filter {samaccountname -eq $_.SamAccountName}  -Properties displayName, company, title, department

}  | Sort-Object company,displayName | Format-Table displayName,company,department,title -GroupBy company -AutoSize

Extract group members from Active Directory and export to CSV file

In order to export Active Directory group membership to a text file, add the following command:

| Out-File -Width 5000 "C:\PS\ADGroupUsersByCompany.txt"

To export the list to the CSV csv, add the following pipe:

| Export-Csv -NoTypeInformation .ADGroupUsersByCompany.csv -Encoding Unicode

You can calculate the total number of users in a group:

(Get-ADGroupMember -Identity Administrators).Count

Here is another useful example. Let’s try to find all AD groups containing the Admin keyword in the name, and display the users that are added in these groups. To display only unique objects, use the -uniq argument:

Get-ADGroup -filter 'SamAccountName -like "*Admin*"' | Get-ADGroupMember -recursive|Select-Object -uniq

In some cases, you can face error, which occurs during the execution of the Get-ADGroupMember command:

Get-ADGroupMember : The specified directory service attribute or value does not exist

This means that the group includes users from other forests (foreign security principals). The Get-ADGroupMember cmdlet doesn’t support working with users of different forests in Active Directory.

Common Errors and Troubleshooting

You may encounter various common errors while working with PowerShell and Active Directory. Here’s how you might tackle them:

Error: “The specified directory service attribute or value does not exist” – This error occurs when the group includes users from other forests (foreign security principals). The Get-ADGroupMember cmdlet doesn’t support working with users of different forests in Active Directory.

In this case, you would need to use alternate methods to pull these group members or work directly within the foreign forest.

Error: “Get-ADGroupMember: The size limit for this request was exceeded” – This error is typically thrown when using Get-ADGroupMember on a group with over 5000 members. A potential workaround involves retrieving all users using the Get-ADGroup and Get-ADUser cmdlets.

Remember, error messages are your friends. They provide valuable insights about the issues and often point you in the right direction to resolve them.

Best Practices

When managing Active Directory group memberships using PowerShell, consider the following best practices:

• Least Privilege: Run scripts with the minimum permissions necessary to perform the task. Avoid using a domain admin account for routine tasks that can be performed with lower privileges.
• Use Comments: It’s important to comment on your scripts, especially when they start to get complex. This will allow you (or someone else) to understand what each part of the script is doing when revisiting it in the future.
• Error Handling: Include proper error handling in your scripts. Try using Try/Catch/Finally blocks to gracefully handle potential errors and exceptions.
• Test Before Deployment: Always test your scripts in a controlled, non-production environment before deploying them on a live system.

Advanced Techniques

For those looking for more advanced techniques, here are some ways you can use PowerShell and Active Directory more effectively:

Automation with Task Scheduler: You can automate exporting group memberships by scheduling your PowerShell script to run at specific intervals using Windows Task Scheduler. This is especially useful for regular auditing of group memberships.

Creating Custom Functions: If you use similar code blocks repeatedly in your scripts, consider creating custom functions. This helps reduce code redundancy and makes your scripts more organized and manageable.

Use PowerShell Remoting: PowerShell remoting allows you to execute commands on remote systems. You can use this to interact with Active Directory on a remote domain controller, enabling you to manage your Active Directory from any system in your network.

Frequently Asked Questions

1. Why is exporting Active Directory group memberships to a CSV file important?

Exporting particular group memberships to a CSV file provides a clear, concise and editable snapshot of your group structures. This can be essential for auditing, troubleshooting, or planning purposes, and also offers a form of backup should the need to restore information arise.

2. What does the PowerShell cmdlet ‘Get-ADGroupMember’ do?

The ‘Get-ADGroupMember’ cmdlet in PowerShell retrieves the members of an AD group. You can use it to list all users, computers, and sub-groups within a particular AD group, making it invaluable for understanding group composition.

3. How can I ensure my exported CSV file is in a user-friendly format?

Exporting the data with appropriate headers and well-structured data is the first step. The ‘Export-Csv’ cmdlet in PowerShell allows you to specify a delimiter, which can help in organizing the data for easy interpretation. It’s also beneficial to choose a logical order for the columns and clean up any unnecessary data prior to exporting.

4. What can I do if ‘Get-ADGroupMember’ cmdlet fails due to large AD groups?

Due to its default limitation, the ‘Get-ADGroupMember’ cmdlet can fail if the group has more than 5000 members. In such cases, you can use the ‘Get-ADGroup’ and ‘Get-ADUser’ cmdlets together with a foreach loop to fetch all members reliably.

5. Are there alternative tools to PowerShell for exporting AD group memberships to a CSV file?

Several third-party tools offer easy-to-use graphical interfaces for managing AD tasks, including exporting group memberships to CSV files. Tools like SolarWinds, ManageEngine ADManager, and others can be helpful if you’re uncomfortable with PowerShell scripting.

6. What permissions are required to use the Get-ADGroupMember cmdlet?

While you don’t need to be a member of the Domain Admins group to use the Get-ADGroupMember cmdlet, you need certain permissions in Active Directory. Any authenticated domain user can obtain information about Active Directory users and groups using the Active Directory (AD) PowerShell module. However, you will require the appropriate permissions if you need to make changes or manage groups.

7. How can I use PowerShell to add a user to an AD group?

Adding a user to an AD group can be done using the Add-ADGroupMember cmdlet. For example, to add a user with the username “jdoe” to the group “IT Department”, you would use the command Add-ADGroupMember -Identity ‘IT Department’ -Members ‘jdoe’.

8. Why does the ‘Get-ADGroupMember’ command fail with large AD groups?

The ‘Get-ADGroupMember’ cmdlet has a default limit and can fail if the group has more than 5,000 members. In such cases, you can use the ‘Get-ADGroup’ and ‘Get-ADUser’ cmdlets together with a foreach loop to fetch all members reliably.

9. Can I modify the properties of an AD user with PowerShell?

Yes, you can modify the properties of an AD user with the Set-ADUser cmdlet. For example, to change a user’s job title, you might use the command Set-ADUser jdoe -Title ‘New Title’.

10. Is it possible to retrieve the manager of an AD group member using PowerShell?

Absolutely. You can do this by first fetching the group member with ‘Get-ADGroupMember’ and then getting the manager with ‘Get-ADUser’. The command would look something like Get-ADUser (Get-ADGroupMember -Identity ‘GroupName’ | Select-Object -ExpandProperty SamAccountName) -Properties Manager.

Wrapping up

The ability to export Active Directory group memberships to CSV files provides a simplified, organized snapshot of your IT environment. This functionality is invaluable for regular auditing and aids in troubleshooting, planning, and maintaining an extra layer of data backup.

We’ve looked at the practical usage of the ‘Get-ADGroupMember’ and ‘Export-Csv’ cmdlets in PowerShell, enabling you to export group memberships effectively. We’ve also looked at handling exceptions for large AD groups, which can be addressed using a combination of ‘Get-ADGroup’ and ‘Get-ADUser’ cmdlets.

However, by using PowerShell scripting, you can effectively introduce automation into the mix when managing and retrieving users in your Active Directory environment.