Get AD Group and its Members

pula25 · September 17, 2017, 2:05pm

Hi Friends,

I need your help to sort this scenario, where I need to get list of AD group and its members along with its MemberOf property in to CSV file. I’ve prepared a script by combining few article available over internet however I’ve stuck to pull “MemberOf” property value in to a CSV. Here is what I’ve prepared so far, please help me to get “MemberOF” property of a AD group.

$OU = Get-Content C:\OU.txt

$Groups = Get-ADGroup -Filter * -Properties * -SearchBase $OU

$Table = @()

$Record = @{
“Group Name” = “”
“Group Description” = “”
“Group Location” = “”
#“Member Of” = “”
“Members” = “”
“UserSAMName” = “”
}

Foreach($Group In $Groups)

{ $Arrayofmembers = Get-ADGroupMember -identity $Group -recursive | select name,samaccountname



    foreach ($Member in $Arrayofmembers)
    {
        $Record."Group Name" = $Group.SamAccountName
        $Record."Group Description" = $Group.Description
        $Record."Group Location" = $Group.CanonicalName
        #$Record."Member Of" = $Group.MemberOf
        $Record."Members" = $Member.name
        $Record."UserSAMName" = ($Member.samaccountname)
        $objRecord = New-Object PSObject -property $Record
        $Table += $objrecord
    }
}

$Table | select “Group Name”, “Group Description”, “Group Location”, “Member Of”, “Members”, “UserSAMName” `
| Export-Csv c:\2.csv -NoTypeInformation

jcakes702 · September 17, 2017, 11:02pm

Remove the # on the MemberOf object in your $Record table and from the Foreach Loop

pula25 · September 17, 2017, 11:33pm

Hi John,

I used # to silent the lines to see other functions of the script.

I need ur help to construct the output from that line. I just comment that line with # to see other functions output.

Thanks
Br,
Pulakesh

erik-sundin · September 18, 2017, 5:04am

There are probably better ways (regex), but this is one way to do it:

$Record."Member Of" = ($group.memberof | %{($_ -split ",")[0].Substring(3)}) -join ","

pula25 · September 18, 2017, 2:51pm

Bingo…

Thanks Erik, thanks a lot for this. It will be nice if you also explain me this (%{($_ -split “,”)[0].Substring(3)}) -join “,” ).

I’m poor in that part. However I was trying this ($Record.“Member Of” = $Group.MemberOf -split (“,”) | Select-String -AllMatches “CN=”) which give me result but with (CN=). but with your trick it works as I was looking for. You make my day friend.

Thanks Again
Br,
Pulakesh

erik-sundin · September 19, 2017, 1:34am

No problem, well % is just an alias for Foreach-Object, $_ is the incoming object and the split operator creates an array. [0] picks the first entry in that array (counting starts at 0, 1 would give you the second entry).
With the substring method you pick the start position on the string, since counting start at 0, using 3 gets you the 4th letter as a starting position (after CN=), you could use this with your code to remove the CN=. If you find more then one group, the code will create an array with an entry for each group, the join operator converts the array to a string with separators of your choice (in this case “,”). You could write the code like this:

$GroupArray = @()
$Group.MemberOf | ForEach-Object{
    $gArray = $_ -split ","
    $g = $gArray[0]
    $GroupArray += $g.Substring(3)
    }
$Record."Member Of" = $GroupArray -join ","

cm-it · September 19, 2017, 2:49am

If you would wanna do it with regex it would be like this:

$Groups = Get-ADGroup "whatever" -prop MemberOf | Select memberOf -ExpandProperty MemberOf 
$Output = $null
foreach ($group in $groups){
    $group -match '(?ms)^CN=(.*?),' | out-null
    $output += @([pscustomobject]@{
            Groups=$matches[1]
    })
 }

this is a lot easier in my opinion

cm-it · September 19, 2017, 2:52am

So i read your post the other day and it got my playing around.
I made my own post about it over on reddit:

https://www.reddit.com/r/PowerShell/comments/70uioq/how_to_export_multiple_arrays_into_a_csv/

Simply because me trying to solve it, caused me to have a difficulty myself.
Getting the data is easy, but getting it into that CSV is what messes me up.

Anyway, the best i’ve come up with is the following code.
It’s not perfect and you can read the process over on reddit.

			$groups =  Get-ADGroup -ldapfilter "(name=g*)"  -Properties * | 
			where { $_.MemberOf -NE "" } | Select Name -first 10
			# Create the columns for the CSV File
			$H1='Group'
			$H2='Members'
			$H3='MemberOf'

			# Add the columns into a variable that will be used to add data
			$row = "" | Select-Object $H1,$H2,$H3

			foreach($group in $groups){
				
				$MemberOf = Get-ADPrincipalGroupMembership -Identity $group.name | Select Name -ExpandProperty Name
				$Members = Get-ADGroupMember -Identity $group.name | select Name -ExpandProperty Name   

				IF($MemberOf.count -gt $members.Count){

					for($i=0;$i -lt $MemberOf.Count; $i++)
					{ 
						$Output=@()
						$row.$H1 = $Group.Name
						$row.$H2 = $members[$i]        
						$row.$H3 =  $MemberOf[$i]
						$Output = $Output + $row
						$Output
					}
				
				}else{
					for($i=0;$i -lt $Members.Count; $i++)
					{ 
						$Output=@()
						$row.$H1 = $Group.Name
						$row.$H2 = $members[$i]  
						if($memberof.count -gt 1){      	# I dont know why i need this, i just need it, otherwise if there is only 1 group
						$row.$H3 =  $MemberOf[$i]       # it splits the groupname up in characters. Disatvantage is that it shows the group
						}else{$row.$H3 =  $MemberOf} # for each member.
						$Output = $Output + $row
						$Output
					}

				}

			  }

erik-sundin · September 19, 2017, 3:10am

Yeah, one of these days I need to learn more about regular expressions

cm-it · September 19, 2017, 3:14am

I just got into it myself.
If you want to know more about how i got into it: https://cookiecrumbles.github.io/Learning-REGEX/

I reference a very good github page where you learn the important stuff with just a few pages.
After that it’s just practice. In that blogpost you see how i got into it.

erik-sundin · September 19, 2017, 3:52am

Thank you, I will check it out.

cm-it · September 19, 2017, 4:30am

if(Test-Path C:\temp\2.csv){Remove-Item C:\temp\2.csv -Force}

$groups =  Get-ADGroup -ldapfilter "(name=*)" -Properties Name,MemberOf | 
where { $_.MemberOf -NE ""} | Select Name -first 20

# Create the columns for the CSV File
$H1="Group"
$H2="Members"
$H3="MemberOf"

# Add the columns into a variable that will be used to add data
$row = "" | Select-Object $H1,$H2,$H3

foreach($group in $groups)
{
    
    $MemberOf = Get-ADPrincipalGroupMembership -Identity $group.name | Select Name -ExpandProperty Name
    $Members = Get-ADGroupMember -Identity $group.name | select Name -ExpandProperty Name   
    $c = 0 # Counter for MemberOf
    $y = 0 # Counter for Members
    IF($MemberOf.count -gt $members.Count)
    {

        for($i=0;$i -lt $MemberOf.Count; $i++)
        { 
            $Output=@()
                $row.$H1 = $Group.Name
                $row.$H2 = $members[$i]        
                $row.$H3 =  $MemberOf[$i]
            $Output = $Output + $row
            $Output
                 
            $Output | Export-Csv -Append  -Delimiter ";" -NoTypeInformation -Path C:\temp\2.csv -Force
        }
    
    }
    else
    {
        for($i=0;$i -lt $Members.Count; $i++)
        { 
            $Output=@()
                $row.$H1 = $Group.Name
            
                if($members.count -gt 1)
                {                              # IF there is only one member, we would have the same problem as with MemberOf
                    $row.$H2 = $members[$i]    # where it would loop trough the characters of the string and not through an array.                
                }
                else
                {                
                    if($y -lt 1)
                    {
                        $row.$H2 =  $Members.Name.ToString()
                        $y++
                    }
                    else
                    {
                        $row.$H2 =  ""
                    }
                }    
              
                if($memberof.count -gt 1)
                {      
                    $row.$H3 =  $MemberOf[$i]                
                }
                else
                {                
                    if($c -lt 1)
                    {
                        $row.$H3 =  $MemberOf.Name.ToString()
                        $c++
                    }
                    else
                    {
                        $row.$H3 =  ""
                    }
                }    
            $Output = $Output + $row
            $Output
                 
            $Output | Export-Csv -Append -Delimiter ";"  -NoTypeInformation -Path C:\temp\2.csv -Force
        }

    }

 }

This should produce the list you are looking for.

Topic		Replies	Views
Help acquiring AD properties PowerShell Help	0	115	December 31, 2011
AD Get AD user groups and sort/save PowerShell Help	0	145	December 31, 2011
Getting Members of Groups in AD PowerShell Help	1	167	August 3, 2017
Get AD User and Group Membership and export to CSV PowerShell Help	2	203	September 26, 2019
AD Get AD user groups and sort/save PowerShell Help	0	148	December 31, 2011

Get AD Group and its Members

Related Topics