Export Users Member of to Excel

PowerShell Help
1

Hi

I am new to Powershell and I am a bit stuck on looking up 200 staff AD accounts via ‘SamAccountName’ and exporting their access groups (under ‘Member Of’), too an excel sheet in a clean format.

So if staff come back and need access to all the groups again (or some), I would like to look up User ID (SamAccountName) in the excel sheet and just copy data back into AD (or maybe just one or two groups need to be added back in).

This is what I have at the moment but I would like exported csv to have USer ID (SamAccountName) in one Column and access groups in another.

I have Googled; tried various formats and now my mind has just gone blank!

$userlist = Get-Content ‘C:\GS\Get Group List\User.txt’

Get-ADUser -Filter ‘*’ -Properties memberof | Where-Object {$userlist -contains $.SamAccountName
} | ForEach-Object {$username = $$groups = $_ | Select-Object -Expand memberof |ForEach-Object { (Get-ADGroup $_).Name }
“{0}: {1}” -f $username, ($groups -join ', ')
} | Out-File ‘C:\GS\Get Group List\Access Groups.csv’

Many Thanks

Gary

2

Hi Gary,

How about this as a starter for 10?

$userlist = Get-Content 'C:\GS\Get Group List\User.txt'

foreach ($user in $userlist){


$Groups = (Get-ADPrincipalGroupMembership $user).Name

$GroupsCSV = $Groups| ForEach-Object {$_ + ','}

"`"$user`",`"$GroupsCSV`"" | out-file export.csv -append       



}

Not fully tested… so may need some tweaking…

3

you can combine the foreach statement body to a oneliner.

foreach($User in $UserList){
 Get-ADPrincipalGroupMembership -Identity $User | Select-Object @{E={$_.Name};L='GroupName'},@{E={$User};L='UserName'} | Export-Csv -Path c:\GroupList.csv -NoTypeInformation -Append
}
4

I ask this purely as a learning point for me…

but is there any performance gains doing it in the one-liner you have written, as opposed to the multi-line version from me?

Just for clarification I wrote it the way I did as its how my brain works… write one line > build on it > next line/function etc.

I suppose this is the beauty of programming\scripting…there is always more than one way to complete a specific task.

Ta!

5

@kvprasoon: Your implementation would overwrite the CSV for every loop iteration.

@Tom_Mort_Yates: My most common answer to everything, it depends. Guessing from how you are building your code, you learned in another language like vbScript or something as the code isn’t “Powershell’y”. Part of the power of Powershell is the ability to pipe objects to other cmdlets, so you’re not leveraging this functionality when you break things out into separate lines.

The best way that I’ve seen and used to build object is like so (shout out to @Dave Wyatt):

...
#Assign variable to loop, any returns roll up to this variable
$results = foreach($User in $UserList){
     Get-ADPrincipalGroupMembership -Identity $User | 
         Select-Object @{E={$_.Name};L='GroupName'},@{E={$User};L='UserName'}
}
#Pipe all of the results to the CSV
$results |  Export-Csv -Path c:\GroupList.csv -NoTypeInformation
6

Thanks Rob - really useful. Absolutely spot on with where my coding came from, amongst other languages!

Will try and do more pipelining where appropriate. Love the example with the implementation of custom expressions.

Learning so much here :slight_smile:

 

7

Good catch, I missed -Append

8

Thank you so much, that worked.