How to Find all Members of Windows Security Group in PowerShell, Assign to Variable Names and Output to CSV?

Question

I'm not a sysAdmin/PowerShell guy per se, but am having trouble getting this Powershell query to work.

The need is to pull back all users in a specific Windows Security Group and format them in a specific way (shown below), however am having some problems identifying why my filter for Security Groups is not working and wonder if there's something wrong with how I have the filter set up.

#Snippet $searcher.filter = "(&(memberof=CN=My Windows Security Group,OU=SecurityGroups,OU=Messaging,OU=Enterprise,DC=****,DC=****)(objectCategory=*))" $results = $searcher.findall() 

I have a breakpoint set up in PowerShell ISE however $results doesn't contain any values while debugging.

I modeled this from an existing script from a colleague, where it looks like he's querying a DL, and am seeing $results values when debugging this:

#--------------------------------------------------------- #DL Filter Example (works) #--------------------------------------------------------- $Searcher.Filter = "(&(memberOf=CN=DL_TestApp_Admin,OU=Distribution Lists,OU=Messaging,OU=Enterprise,DC=dcInfo,DC=dcInfo,DC=****)(objectCategory=*))" $results = $searcher.findall() 

Here's the entire PS script (modified by me) that filters Windows Security Groups

$curdate = (Get-Date).ToString("MMddyyyy") $baseDN = "LDAP://" $Searcher = New-Object DirectoryServices.DirectorySearcher $searcher.searchroot = new-object system.directoryservices.directoryentry($basedn) #--------------------------------------------------------- #Windows Security Group Filter (doesn't work) #--------------------------------------------------------- $searcher.filter = "(&(memberof=CN=My Windows Security Group,OU=SecurityGroups,OU=Messaging,OU=Enterprise,DC=****,DC=****)(objectCategory=*))" $results = $searcher.findall() #--------------------------------------------------------- #Build the dataset in specific format #--------------------------------------------------------- $usercsv = $results | #-Object { New-Object -TypeName PSObject -Property @{ "User ID" = $_.properties['samAccountName'][0] "User Role" = "Blah" "Elevated Role" = "Y" "Internal User" = "Y" "Date of Last Login" = $null "App Specific Info 1" = $null "App Specific Info 2" = $null "Account Creation Date" = $null "Last Password Change Date" = $null "User Email Address" = $_.properties['mail'][0] "User First Name" = $null "User Last Name" = $null "App Specific Info 3" = $null "Review Group" = $null } } #export to csv $usercsv | Select-Object -Property "User ID","User Role","Elevated Role","Internal User","Date of Last Login","App Specific Info 1","App Specific Info 2","Account Creation Date","Last Password Change Date","User Email Address","User First Name","User Last Name","App Specific Info 3","Review Group" | Export-Csv -NoTypeInformation -Path "UsersListing_$curdate.csv" Write-Host "Wrote", $results.Count, "record(s)" #removing last CR LF #TODO: Move this to a common function in a separate script for all scripts to call $stream = [IO.File]::OpenWrite("UsersListing_$curdate.csv") $stream.SetLength($stream.Length - 2) $stream.Close() $stream.Dispose() 

Originally I had this written to run in a DevOps Poweshell release pipeline as follows to produce a csv file:

$Members = Get-ADGroup -Filter {Name -eq "WSG"} -Properties Member | Select-Object -ExpandProperty Member $GlobalCatalog = "$((Get-ADDomainController -Discover).Name):xxxx" $output = Foreach ($User in $Members) { Get-ADUser -Identity $User -Server $GlobalCatalog -Properties CN, EmailAddress, AccountExpirationDate, Created, HomePage, LastLogonDate, PasswordLastSet, whenCreated | Select-Object CN, SamAccountName,EmailAddress, AccountExpirationDate, Created, Enabled, HomePage, LastLogonDate, Name, PasswordLastSet, UserPrincipalName, whenCreated } #output csv file $output | Export-Csv $Env:TEMP\Users.csv -NoTypeInformation 

In this case, it didn't matter the output format and I am not sure if I can modify this query to output the results into this format:

 "User ID" = $_.properties['samAccountName'][0] "User Role" = "Blah" "Elevated Role" = "Y" "Internal User" = "Y" "Date of Last Login" = $null "App Specific Info 1" = $null "App Specific Info 2" = $null "Account Creation Date" = $null "Last Password Change Date" = $null "User Email Address" = $_.properties['mail'][0] "User First Name" = $null "User Last Name" = $null "App Specific Info 3" = $null "Review Group" = $null 

And the results from this query are in this particular format:

CN : SamAccountName : EmailAddress : AccountExpirationDate : Created : Enabled : HomePage : LastLogonDate : Name : PasswordLastSet : UserPrincipalName : whenCreated : 

Thanks in advance for any assistance.

Answer 1

There is no need to bother with an actual LDAP search since the Active Directory PowerShell module has been introduced in Windows Server 2008 R2.

Get-ADGroupMember is what you are looking for. If you then need further details about the group members, you can use Get-ADUser on them to obtain whatever information you need.

The AD PowerShell module is automatically installed on all Domain Controllers, but you can install it on any Windows Server system by enabling the corresponding Windows feature:

Install-WindowsFeature RSAT-AD-PowerShell 

You can also install it on Windows client systems as part of the Remote Server Administration Tools.

Answer 2

Here's the final Poweshell that was developed to get the results we were after:

$curdate = (Get-Date).ToString("MMddyyyy") #------------------------------------------------------------------------------- # Get members for Windows Security Groups #------------------------------------------------------------------------------- $Members = Get-ADGroup -Filter {Name -eq "My AD Group"} -Properties Member | Select-Object -ExpandProperty Member $GlobalCatalog = "$((Get-ADDomainController -Discover).Name):1234" $results = @() Foreach ($User in $Members) { $userInfo = Get-ADUser -Identity $User -Server $GlobalCatalog -Properties SamAccountName, EmailAddress, LastLogonDate $details = [ordered] @{ "Active Directory ID" = $userInfo.SamAccountName "User Role" = "My AD Group" "Elevated Role" = "N" "Internal User" = "Y" "Date of Last Logon" = $userInfo.LastLogonDate "Application Specific Info 1" = $null "Application Specific Info 2" = $null "Acct Creation Date" = $null "Last PWD Change Date" = $null "User Email Address" = $userInfo.EmailAddress "User First Name" = $null "User Last Name" = $null "Application Specific Info 3" = $null "Review Group" = $null } $results = New-Object PSObject -Property $details } #--------------------------------------------------------- #output results to csv #--------------------------------------------------------- $results | Export-Csv -NoTypeInformation -Path "UsersListing_$curdate.csv" 

The variable names (Active Directory ID, User Role, etc) represent the column headers in the CSV file.

Hope it helps someone.

Answer 3

You need to specify a base DN. This is typically the domain root DN.