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)", $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)", $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
            $members = [PSCustomObject]@{
                Server = $member.PSComputerName
                Group  = $member.GroupComponent.Name
                Domain = $member.PartComponent.Domain
                Member = $member.PartComponent.Name
            }
            $total += $members
        }
    }

    #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