For one of our customers, we needed a report on all local groups, and their members on all Active Directory joined servers. You can connect all servers one by one with Computer Management and check… But, luckily, PowerShell is there for you π In this blog post, I will show you an easy way to get a report on the memberships, and yes… I used the ImportExcel module, too (Needed for the Export-Excel cmdlet) π
How the script works
The script gathers all active Computer Accounts in your Active Directory in the last 31 days (Based on the LastLogonDate attribute) and uses the Get-Ciminstance cmdlet to avoid checking inactive servers with the Win32_GroupUser ClassName to retrieve all users/groups on the servers found. It then reports it to the file you specify, and depending on whether it’s a .csv or .xlsx file, it will be exported to a CSV file with a semicolon delimiter or a formatted Excel sheet. Empty groups are skipped. The report only contains groups with users or groups in them.
Three parameters can be used:
- the -ComputerNameFilter parameter can be used to search for computer accounts containing ABC.
- The -GroupNameFilter can be used to filter for groups, for example, ‘Administrators’,’Remote Desktop Users’,’Users’
- The -OUfilter parameter can be used to search for computers matching the path given, for example, ‘OU=Computers, OU=Corp, DC=Test, DC=Local” (This is recursive from the path given towards lower OUs.
- The -FileName parameter will scan the servers’ names mentioned in the specified file. The file must consist of the server name(s) which need(s) to be scanned and only contain one server name per line.
Running the script
With the Active Directory PowerShell module installed, you can run the script on any Domain Joined machine. After running the script, the Get-LocalGroupMembers cmdlet is available. When you run Get-LocalGroupMembers, it will prompt for an Outfile if you don’t specify the -OutFile parameter. Specify a complete path (C:\temp\members.xlsx or c:\temp\members.csv), and it will export the results in that format in that location. You can also use one of the two parameters (Not both simultaneously) to enter a filter for the Computer Account Name of OU that the Computer Account is a member of.
In the example below, I ran the script on my test environment containing two Windows Server 2022 member servers.
Get-LocalGroupMembers -OutFile c:\temp\members.xlsx
Retrieving local groups and their members on server W2K22SRV1 [W2K22SRV1] Adding Administrator from domain or server W2K22SRV1 which is member of the local group 'Administrators' [W2K22SRV1] Adding Domain Admins from domain or server TEST which is member of the local group 'Administrators' [W2K22SRV1] Adding user2 from domain or server TEST which is member of the local group 'Administrators' [W2K22SRV1] Adding Guest from domain or server W2K22SRV1 which is member of the local group 'Guests' [W2K22SRV1] Adding IUSR from domain or server W2K22SRV1 which is member of the local group 'IIS_IUSRS' [W2K22SRV1] Adding DefaultAccount from domain or server W2K22SRV1 which is member of the local group 'System Managed Accounts Group' [W2K22SRV1] Adding INTERACTIVE from domain or server W2K22SRV1 which is member of the local group 'Users' [W2K22SRV1] Adding Authenticated Users from domain or server W2K22SRV1 which is member of the local group 'Users' [W2K22SRV1] Adding Domain Users from domain or server TEST which is member of the local group 'Users' Retrieving local groups and their members on server W2K22SRV2 [W2K22SRV2] Adding Administrator from domain or server W2K22SRV2 which is member of the local group 'Administrators' [W2K22SRV2] Adding Domain Admins from domain or server TEST which is member of the local group 'Administrators' [W2K22SRV2] Adding user1 from domain or server TEST which is member of the local group 'Administrators' [W2K22SRV2] Adding Guest from domain or server W2K22SRV2 which is member of the local group 'Guests' [W2K22SRV2] Adding IUSR from domain or server W2K22SRV2 which is member of the local group 'IIS_IUSRS' [W2K22SRV2] Adding DefaultAccount from domain or server W2K22SRV2 which is member of the local group 'System Managed Accounts Group' [W2K22SRV2] Adding INTERACTIVE from domain or server W2K22SRV2 which is member of the local group 'Users' [W2K22SRV2] Adding Authenticated Users from domain or server W2K22SRV2 which is member of the local group 'Users' [W2K22SRV2] Adding Domain Users from domain or server TEST which is member of the local group 'Users' Exported results to c:\temp\members.xlsx
The exported results look like this for the exported members.xlsx:

And when exported to CSV, it looks like this (No formatting, auto filter, etc.)

The script
Below are the contents of the function Get-LocalGroupMembers script:
function Get-LocalGroupMembers { [CmdletBinding(DefaultParameterSetName = 'All')] param ( [parameter(Mandatory = $true)][string]$Outfile, [parameter(Mandatory = $false, parameterSetName = "ComputerNameFilter")][string]$ComputerNameFilter, [parameter(Mandatory = $false, parameterSetName = "OUFilter")][string]$OUfilter, [parameter(Mandatory = $false, parameterSetName = "FileName")][string]$FileName, [parameter(Mandatory = $false)][string[]]$GroupNameFilter ) #Check file extension, if it's not .csv or .xlsx exit if (-not ($Outfile.EndsWith('.csv') -or $Outfile.EndsWith('.xlsx'))) { Write-Warning ("The specified {0} output file should use the .csv or .xlsx extension, exiting..." -f $Outfile) return } #Check is ActiveDirectory module is installed if (-not (Get-Module -ListAvailable | Where-Object Name -Match ActiveDirectory)) { Write-Warning ("ActiveDirectory PowerShell Module was not found, please install before running script...") return } #Retrieve all enabled computer accounts of Domain Member servers which updated their computer account the last 30 days, skip Domain Controllers #Skip checks if -FileName parameter was used #Using $ComputerNameFilter if ($ComputerNameFilter) { $servers = Get-ADComputer -Filter { (OperatingSystem -like 'Windows Server*') -and (PrimaryGroupID -ne '516') -and (Enabled -eq $TRUE) } -Properties LastLogonDate ` | Where-Object Name -Match $ComputerNameFilter ` | Where-Object LastLogonDate -gt (Get-Date).AddDays(-31) ` | Sort-Object Name } #Using OUfilter if ($OUfilter) { $servers = Get-ADComputer -Filter { (OperatingSystem -like 'Windows Server*') -and (PrimaryGroupID -ne '516') -and (Enabled -eq $TRUE) } -Properties LastLogonDate ` | Where-Object DistinguishedName -Match $OUfilter ` | Where-Object LastLogonDate -gt (Get-Date).AddDays(-31) ` | Sort-Object Name } #Without a Name or OU filter if (-not $OUfilter -and -not $ComputerNameFilter) { $servers = Get-ADComputer -Filter { (OperatingSystem -like 'Windows Server*') -and (PrimaryGroupID -ne '516') -and (Enabled -eq $TRUE) } -Properties LastLogonDate -ErrorAction Stop ` | Where-Object LastLogonDate -gt (Get-Date).AddDays(-31) ` | Sort-Object Name } #From specified filename if ($FileName) { try { $servers = get-content -Path $FileName -ErrorAction Stop } catch { Write-Warning ("Error accessing/reading {0}. Exiting" -f $FileName) return } } #Exit if no computer accounts were found if ($servers.count -eq 0) { Write-Warning ("No Computer Accounts were found. Check access, filters or contents of file. Exiting...") return } $total = foreach ($server in $servers) { #Retrieve all local groups on the server and their members if (-not $FileName -and -not $GroupNameFilter) { try { $groupmembers = Get-CimInstance -ClassName Win32_GroupUser -ComputerName $server.name -ErrorAction Stop | Where-Object GroupComponent -Match $server.Name Write-Host ("`nRetrieving local groups and their members on server {0}" -f $server.name) -ForeGroundColor Green } catch { Write-Warning ("Could not connect to {0}, skipping..." -f $server.Name) Continue } } if ($FileName -and -not $GroupNameFilter) { try { $groupmembers = Get-CimInstance -ClassName Win32_GroupUser -ComputerName $server -ErrorAction Stop | Where-Object GroupComponent -Match $server Write-Host ("`nRetrieving local groups and their members on server {0}" -f $server) -ForeGroundColor Green } catch { Write-Warning ("Could not connect to {0}, skipping..." -f $server) Continue } } if ($FileName -and $GroupNameFilter) { Write-Host ("Retrieving members of the groups {0} on server {1}" -f "$($GroupNameFilter)".replace(' ', ', '), $server) -ForeGroundColor Green foreach ($group in $GroupNameFilter) { try { $groupmember = Get-CimInstance -ClassName Win32_GroupUser -ComputerName $server -ErrorAction Stop | Where-Object { $_.GroupComponent -Match $server -and $_.GroupComponent.Name -eq $group } $groupmembers += $groupmember } catch { Write-Warning ("Could not connect to {0} or find group {1}, skipping..." -f $server, $group) Continue } } } if (-not $FileName -and $GroupNameFilter) { Write-Host ("Retrieving members of the groups {0} on server {1}" -f "$($GroupNameFilter)".replace(' ', ', '), $server.name) -ForeGroundColor Green foreach ($group in $GroupNameFilter) { try { $groupmember = Get-CimInstance -ClassName Win32_GroupUser -ComputerName $server.name -ErrorAction Stop | Where-Object { $_.GroupComponent -Match $server.Name -and $_.GroupComponent.Name -eq $group } $groupmembers += $groupmember } catch { Write-Warning ("Could not connect to {0} or find group {1}, skipping..." -f $server.Name, $group) Continue } } } #Loop through all groupmembers and add them to the $total variable foreach ($member in $groupmembers) { Write-Host ("[{0}] Adding {1} from domain or server {2} which is member of the local group '{3}'" -f $member.PSComputerName, $member.PartComponent.Name, $member.PartComponent.Domain, $member.GroupComponent.Name) -ForegroundColor Green [PSCustomObject]@{ Server = $member.PSComputerName Group = $member.GroupComponent.Name Domain = $member.PartComponent.Domain Member = $member.PartComponent.Name } } } #Output to report is resuls where found if ($total.count -gt 0) { #Export results to either CSV of XLSX, install ImportExcel module if needed if ($Outfile.EndsWith('.csv')) { try { New-Item -Path $Outfile -ItemType File -Force:$true -Confirm:$false -ErrorAction Stop | Out-Null $total | Sort-Object Server, Group, User | Export-Csv -Path $Outfile -Encoding UTF8 -Delimiter ';' -NoTypeInformation Write-Host ("`nExported results to {0}" -f $Outfile) -ForegroundColor Green } catch { Write-Warning ("`nCould not export results to {0}, check path and permissions" -f $Outfile) return } } if ($Outfile.EndsWith('.xlsx')) { try { #Test path and remove empty file afterwards because xlsx is corrupted if not New-Item -Path $Outfile -ItemType File -Force:$true -Confirm:$false -ErrorAction Stop | Out-Null Remove-Item -Path $Outfile -Force:$true -Confirm:$false | Out-Null #Install ImportExcel module if needed if (-not (Get-Module -ListAvailable | Where-Object Name -Match ImportExcel)) { Write-Warning ("`nImportExcel PowerShell Module was not found, installing...") Install-Module ImportExcel -Scope CurrentUser -Force:$true Import-Module ImportExcel } $total | Sort-Object Server, Group, User | Export-Excel -AutoSize -BoldTopRow -FreezeTopRow -AutoFilter -Path $Outfile Write-Host ("`nExported results to {0}" -f $Outfile) -ForegroundColor Green } catch { Write-Warning ("`nCould not export results to {0}, check path and permissions" -f $Outfile) return } } } else { Write-Warning ("Could not find any members, please check acces or filter...") return } }
Download the script(s) from GitHub here
I literally can’t live without the import-excel module. It’s so great!
Would it be possible to run this one just a selected amount of servers? Letβs say they were an array in a text file.
Ah, that’s something that could be done. Did this for a report on the whole environment, but perhaps it could be convenient to filter a few things. I’ll look into that in the next few days
Added a -ComputerNameFilter and -Oufilter to the script, you can use this to filter for certain servers
Pingback: PowerShell is fun :) Overview of 2022 posts
This is an excellent script and works perfectly. The filters added are also great. I’ve been trying to tweak it a bit as the script pulls “all” local groups that have any users in them, which ends up being far more information than I usually need. I’m wondering if one additional optional filter parameter might be the cherry on top. Essentially a filter to let you specify which specific local groups you want to check. Something like Get-LocalGroupMembers -LGfilter “Administrators”,”Remote Desktop Users”
Thanks again for the amazing script!
I added the GroupNameFilter parameter and updated the blog+GitHub, tested it on my test environments and you can use -GroupNameFilter ‘Administrators’,’Remote Desktop Users’,’Users’ and it will only search for those groups. Let me know if it works for you too π
Works perfectly, thank you again!
Great, no problem and glad to help!