Get Active Directory Group Membership Report in CSV Format
Active Directory group membership report is required in almost every organization that uses an AD. With this type of report, it is a lot easier for administrators to find what groups are there in an organizational unit.
Moreover, it also forms a basis for many business-level decisions, like changing a user’s group/ adding new members, or splitting a group. The main problem is that many admins don’t wish to use the PowerShell script to get their AD group members list. It’s either too complicated or time-consuming, and for some, it’s both.
Also to add to the confusion there are two approaches to visualize the membership data. One is to check on a user level to see which group a user belongs to. Another and frankly better method is to search through the groups themselves and list out the users that are present in them.
No to worry as this is the guide you need to bypass any highly technical requirements. First, let’s see what are the contents of an AD group membership report.
Table of Contents
- What Does an AD Group Membership Report Contain?
- Make AD Group Membership Report With PowerShell
- Why PowerShell is Problematic
- Get AD Group Members List Using CMD
- Professionally Export AD Group Members to CSV without PowerShell
- Automated Steps to Use the Tool
- View Group Member List with ADUC
- Get the AD Group Report using Admin Center
- Check the Event Viewer
- Conclusion
- FAQs
What Does an Active Directory Group Membership Report Tell Us?
With this report, Admins get an overview of the employee distribution inside the OU groups. It also helps to find any inconsistencies in the group structure or membership count. So it is not a surprise that many regard this as the next logical step after admins check the Active Directory user login history for an OU.
It almost always contains the date on which a group was made thus helping to determine for how long has a particular group been active. Moreover, with the help of a modified date, it’s possible for the admins to figure out when exactly someone updated the group settings.
Group membership report also reveals if or not a group is of a special category like a security group or distribution list. All this is present in a tabular format for better readability.
It is not fair to outright reject the PowerShell method without first knowing what it is. After all, it is been a traditional way to make a report. So to get members of AD Group with PowerShell the following section is all that you require
Use PowerShell to List Users in A Group
Here is a sample script:
# Get all groups in the Active Directory $groups = Get-ADGroup -Filter * Clear-Host foreach ($group in $groups) { # Get Active Directory group membership report of the current group $members = Get-ADGroupMember -Identity $group if ($members.Count -eq 0) { # Empty group Write-Host "Group: $($group.Name)" -ForegroundColor Yellow } else { # Group with members Write-Host "Group: $($group.Name)" -ForegroundColor Cyan } # Output each member of the group foreach ($member in $members) { Write-Host " - Member: $($member.Name)" -ForegroundColor Green } }
Note that this is an example script and should be used only after modification from your end. An explanation for this script can be found below.
This script first fetches all the Group category objects from the Active Directory. After that, a loop runs through the acquired Groups individually and checks for members.
When, inside the loop, an if-else logic takes over which displays groups in specific color codes. All empty groups are printed in Yellow. Meanwhile, groups containing even a single member are listed in Cyan, and for the members we use Green.
You may add functionality like determining the Group type or exporting the list to CSV etc. on your end. As we did during the export list of disabled users from Active Directory task. However, be careful as it may break the original PowerShell script or end up permanently damaging your AD group structure.
Why do Admins Want to Export AD Group Members to CSV without PowerShell?
Problems in using PowerShell don’t end if you get the script. On the contrary, it is not wrong to say that the real problems begin afterward. This is because most PowerShell-oriented solutions are generalized scripts with little to no information about your exact AD environment. Not to mention that these scripts often ignore or fail to mention any previous security considerations that the admin should take. Like the one where the dsquery commands are not working in the normal Windows machines.
Even the machines with Windows Server need an AD DS to run this. That is why it ist possible to get members of AD Group with PowerShell in some AD environments.
Simply copy-pasting any random scripts of PowerShell to list users in a group is not going to provide you with the intended results. Moreover, the even more dangerous scenario is when admins get false positive results. Combine this with no direct way of verifying the result. Admins may unknowingly submit/use the wrong data.
As the source data for all intents and purposes near garbage, so the output it produces post-analysis is the same.
Having the script is one thing, but understanding what it does is different. If the script does not run and gets the Active Directory group membership report admins have to fix it manually. In the worst-case scenario, it may trigger a domain-wide account lockout so admins have to reset and check last password change in Active Directory environment.
This leads to more time being spent on troubleshooting the script than doing what’s required. PowerShell is not the only way to check group members in an AD, another somewhat unorthodox approach is to use the default command line.
How to Get AD Group Members List Using CMD?
Another less intrusive code-based method is the Command line. To open and use the command line module
- Press the Windows key + R.
- Type cmd in the run box
- Type the following query.
for /f "delims=" %G in ('dsquery group -limit 0') do @echo Group: %G & dsget group %G -members
The commands we use here work with the following logic which is similar to the one used by PowerShell to list users in a group.
The “dsquery group -limit 0” part oops through all groups in Active Directory. Next for every group we print “Group: “ followed by its name. At last, it then uses the “dsget group” tag to retrieve and display the members of each group.
This should get the required Active Directory group membership report. However, even with this easier approach, administrators don’t have the control that they desire.
Sometimes they need to be more sophisticated and detailed to work so admins with a non-tech background may fail to get accurate results. However, people with the right skill set can exploit these commands(with modifications of course) and get unauthorized access to your AD environment.
There is a sure-shot way to skip over these limitations and that too with a GUI-based software.
Automated Approach to Export AD Group Members to CSV without PowerShell
Hands down the best tool to create a full active directory group membership report is SysTools AD Reporting Tool. It gives the admin full control to view and extract the data about the members of a specific group.
The reporting can be categorized based on the current requirements of your organization. This means if admins want to see the group creation date they choose to get that. Moreover, more complex group membership requests like Distribution or Security groups are also available.
Also Read: Why you need ADMT Replacement for AD Migration
On top of that if admins want to verify whether or not any changes were made to the group. They have the Modified category for exactly this kind of scenario. Now that we have a basic overview of what the tools capabilities are lets see how to use it.
Steps to Get Active Directory Group Membership Report via the Tool
Step 1. Download, launch, and enter “administrator” in the space provided in the User ID and password entry box.
Step 2. Activate the tool and press the “REGISTER DOMAIN CONTROLLER” icon.
Step 3. Type Domain Friendly Name and IP address for your AD hit Save & Continue.
Step 4. You arrive at the Domain details page where you have to put the Admin credentials for your domain and validate them.
Step 5. Go to the Report tab to view the reporting options. Select group as the workload and All as the subcategory.
Step 6. Using the breadcrumb-style filtering menu as the guide you can identify what report you are about to extract. Change the All category to unlock the duration picker.
Step 7. Select a time period out of 5, 7, 10, 30, 60, 90 days, or 1 year starting from the present date. With the custom option, you can set a starting and ending date yourself.
Step 8. Hit the preview button and make a column-wise segregation of Group membership.
Step 9. Click on the arrow within the Download Report button and tap on CSV.
Step 10. Click on the Download option once again that appears inside the popup box.
Step 11. Browse for the location where you want to keep the report data and hit the Save icon in the Save as window.
Step 12. The CSV file can be checked on any text editor.
There are some native GUI methods available in your AD. It would be unfair to skip these methods so here they are.
Active Directory Users and Computers Snap-in to View Group Member List
- Launch the ADUC, select a Domain, and click on the search button from the toolbar.
- In the Find Users, Contacts, and Groups window go to the Advanced tab.
- For the Field section hover over Group and select Members from the Context Menu.
- Then put the condition as “Present”, and click on Add > Find Now.
- Click on a Group from the list.
- Toggle the Members tab in its Properties box.
- This will show you all the users/objects that are part of this group.
To look at the member less groups all you have to do is change the “is present” condition to “Not Present”
After that follow the rest of the steps exactly like before, here when you toggle the Members tab you will see that it is empty.
Unlike ADUC where you can’t copy the results. The Admin Center Results can be put into a Excel sheet by copy pasting. Making it a superior built-in option for admins.
Get the Active Directory Group Membership Report via the Admin Center
- Open ADAC.
- Click on Global Filter from the left-hand pane.
- Toggle the “Convert to LDAP” option.
- Paste the following command:
“(&(objectCategory=group)(objectClass=group)(member=*))”
- Click on Apply
- The first result should be automatically selected, to change the AD group whose member list is required simply select it.
- Then click on the properties option from the right-hand pane.
- Go to the Members tab and view the list.
To export the AD group membership data select all results press Ctrl + C, open a new Excel workbook, or Notepad, and Press Ctrl + V.
Moreover, if you want to get a list of AD Groups with no members in it, just make a small adjustment in the LDAP query. Which is:
“(&(objectCategory=group)(objectClass=group)(!(member=*)))”
The Exclamation mark in front of the member attribute negates it. Meaning that the query will now search for empty Groups.
To confirm check the member list of any of the Groups that appear inside the results in the same way we did for functioning groups.
Although it provides a member list admins still have no clue when each of these members became a part of the Group. Don’t worry your AD has got you covered thanks to Event Viewer. Let’s see how.
Check the Event Viewer and Bypass PowerShell to List Users in a Group
An unorthodox way to track user presence in an Active Directory Group is through the Event Viewer. Every user addition and removal task is logged inside the Event viewer with a corresponding Event ID. So admins can thus get a rough idea of the group membership by registering the entry and exit of members. Combining it with the group creation event can help filter out the Empty groups from the populated ones.
Here are the steps to use the Event Viewer for this task.
- Press the Windows and R keys together on your machine, wait for the Run module to appear, then type “eventvwr.msc” in the text box. Click OK or Press Enter.
- Once the Event Viewer module opens expand the Windows logs and Select Security from the left-hand pane.
Now scroll through the list till you see one or more of the following IDs.
4731: Registers every time after the formation of a new Security Group.
4732: The code for a member addition in an AD Security Group.
4733: You see this when admins remove members from a Security Group.
4749: Indicates a Distribution Group creation.
4751: Triggers when a member is added to a Security Disabled group.
4752: This is the ID that contains information on an object’s exit from a distribution group.
Source: Microsoft
You can use the Find parameter to save some time. Moreover, there is also an option to set a Custom Filter to introduce repeatability. We recommend to follow this practice as you most certainly have to check an AD group membership sometime again in the future. It can also act as a guide for another admin who takes over your role later.
Conclusion
In this guide, we explained how to get an Active Directory group membership report from scratch. Admins saw what complete group data from AD looks like. On top of that, we helped admins understand why PowerShell scripts are not the best approach for extracting group data. As an alternative, we introduced the best option on the market. Use it and free yourself from problematic PowerShell commands.
Frequently Asked Questions
Q. How to use PowerShell to get a security Group member list?
Apply a category filter in the same script under an if-else block.
$group.GroupCategory -eq 'Security'
This filter will restrict the search scope to only include security groups. The rest of the code remains as it is. If you find the process tough you can always use the tool as it comes with an inbuilt filtering option for both security and distribution groups.
Q. Is it possible to export user group membership to csv for further analysis?
Yes, you can apply the export option to PowerShell, the command line module and ADUC also give you the provision to pull out a CSV file after filtering the results. Those who want to use the AD administrative center have to manually select, copy, and paste the results. Further editing may also be required. No such manual intervention is necessary in case of the tool as it generates a CSV ready for use.
Q. I believe I am unable to get members of the AD group via PowerShell or otherwise due to them being empty. Is it possible to know for sure?
The following PowerShell command extracts all empty groups and arranges them in a list Get-ADGroup -Filter * -Properties Members | Where-Object {-not $_.Members} | Select-Object Name
Q. Is it possible to check for nested group memberships in Active Directory?
Yes you have to make a few tweaks in the PowerShell here is a base script to get you started
function Get-NestedGroupMembers { param ( [Parameter(Mandatory=$true)] [string]$GroupName, [int]$Indent = 0 ) # Get the group $group = Get-ADGroup -Identity $GroupName # Write the group name with indentation Write-Host (" " * $Indent) $group.Name -ForegroundColor Cyan # Get the group members $members = Get-ADGroupMember -Identity $group foreach ($member in $members) { if ($member.objectClass -eq 'group') { # If the member is a group, recursively get its members Get-NestedGroupMembers -GroupName $member.SamAccountName -Indent ($Indent + 2) } else { # If the member is a user or other object, write its name Write-Host (" " * ($Indent + 2)) "- Member: $($member.Name)" -ForegroundColor Green } } } # Replace 'YourGroupName' with the name of the root group you want to query $rootGroupName = "YourGroupName" # Start the recursion from the root group Get-NestedGroupMembers -GroupName $rootGroupName