Retrieve all local group members of Active Directory member servers

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 hey… 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 Computer Accounts in your Active Directory that were active in the last 30 days (To avoid checking inactive servers, this is based on the LastLogonDate attribute) and uses the Get-Ciminstance cmdlet together 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 if it’s a .csv or .xlsx file, it will export it 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.

There are also two parameters that can be used:

  • The -ComputerNameFilter parameter can be used to search for computer accounts containing ABC for example
  • 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

Running the script

You can run the script on any Domain Joined machine which has the Active Directory PowerShell module installed. It will prompt for an Outfile if you don’t specify the -Outfile parameter, specify a complete path (For example, c:\temp\members.xlsx or c:\temp\members.csv) and it will export the results in that format there. You can also use the one of the two parameters (Not both at the same time) to enter a filter for the Computer Account Name of OU that the Computer Account is member of.

In the example below, I ran the script on my test environment containing two Windows Server 2022 member servers.

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 {
    param (
        [parameter(Mandatory = $true)][string]$Outfile,
        [parameter(Mandatory = $false)][string]$ComputerNameFilter,
        [parameter(Mandatory = $false)][string]$OUfilter
    )
    
    #Check if both ComputerNameFilter and OUfilter where used
    if ($ComputerNameFilter -and $OUfilter) {
        Write-Warning ("Both COmputerNameFilter and OUfilter were used, these can't be combined. Exiting...")
        return
    }

    #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 intalled
    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

    #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
    }

    #Exit if no computer accounts were found
    if ($servers.count -eq 0) {
        Write-Warning ("No Computer Accounts were found, check access or filters. Exiting...")
        return
    }

    $total = foreach ($server in $servers) {
    
        #Retrieve all local groups on the server and their members
        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
        }
    
        #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 (" results to {0}" -f $Outfile) -ForegroundColor Green
            }
            catch {
                Write-Warning ("nCould not export results to {0}, check path and permissions" -f $Outfile)
            }
        }

        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 (" 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 (" not export results to {0}, check path and permissions" -f $Outfile)
            }
        }
    }
    else {
        Write-Warning ("Could not find any members, please check acces or filter...")
    }
}

Download the script(s) from GitHub here

4 thoughts on “Retrieve all local group members of Active Directory member servers

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.