Author Posts

April 21, 2015 at 12:17 pm

I have become decent at finding the AD data I need but when it comes to creating a spreadsheet with the information I find my knowledge totally inadequate to do what I want to do. I am hoping for some quick help and some helpful URL's.

When I run "Get-aduser MyID -Properties * | FL Name, Memberof " the memberof field has over 100 entries in curly braces separated by commas.
I would like to end up with a spreadsheet with my name in one column and then another column of the 100+ groups, then the next users name, his groups etc.

I have looked at Format-List, and tried Exportto-csv with no luck, what is the best way to do this?

Thanks for any help you can provide,
Lee

April 21, 2015 at 12:24 pm

That's because MemberOf is a collection of objects, not just a simple value; when asked to display a collection in a list or table, PowerShell can't create a sub-list or sub-table, so it does the curly bracket thing.

There's no straightforward way to get what you want using what's built into the shell. You'd have to code up something custom.

April 21, 2015 at 12:25 pm

Get-aduser MyID -Properties memberof |
Select-Object Name,@{n='groups';e={
		$_.memberof -join "`r`n"
	}} |
Export-CSV -NoTypeInformation -Path c:\test.csv

See if that is closer to what you want.

April 21, 2015 at 12:32 pm

Get-aduser MyID -Properties memberof |
ForEach-Object {
	$name = $_.name
	$_.memberof |
	ForEach-Object {
		[pscustomobject]@{
			'Name'=$name
			'Group'=$_
		}
	}
} |
Export-Csv -NoTypeInformation -Path c:\test.csv

Or something like this.

April 21, 2015 at 12:48 pm

Ugly, but it works...

Calculated Expression
Loop through MemberOf
Split the CN into an array
Get the first index (e.g. CN=MyGroup)
Replace CN= with nothing, basically strip off CN=
And finally Join the returned array with a semicolon...

Get-ADUser myuser -Properties MemberOf | Select Name, @{Label="Groups";Expression={$($_.MemberOf | foreach{($_.Split(",")[0].Replace("CN=",""))}) -Join ";"}}

Returns:

Name        Groups                                                                         
----        ------                                                                         
My User Group1;Group2;Group3;Group4;...

April 21, 2015 at 12:52 pm

Thanks Don. I just purchased your PS in 30 days of lunches; does that include how I might handle this?

April 21, 2015 at 12:53 pm

Output has a little extra info, how do I trim it down?

Name Dan, Lee
Group CN=g-cae-NetworkEng-readers,OU=Roles,DC=up,DC=corp,DC=upc
Name Dan, Lee
Group CN=g-jar-read,OU=Roles,DC=up,DC=corp,DC=upc