Export Users Member of to Excel

Welcome Forums General PowerShell Q&A Export Users Member of to Excel

This topic contains 7 replies, has 4 voices, and was last updated by

 
Participant
4 months, 1 week ago.

  • Author
    Posts
  • #129860

    Participant
    Topics: 1
    Replies: 1
    Points: -3
    Rank: Member

    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

  • #129891

    Participant
    Topics: 2
    Replies: 25
    Points: 167
    Helping Hand
    Rank: Participant

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

  • #129899

    Moderator
    Topics: 8
    Replies: 739
    Points: 2,071
    Helping Hand
    Rank: Community Hero

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

      Participant
      Topics: 2
      Replies: 25
      Points: 167
      Helping Hand
      Rank: Participant

      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!

  • #129920

    Participant
    Topics: 8
    Replies: 1190
    Points: 639
    Helping Hand
    Rank: Major Contributor

    @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
    
    
    • #129927

      Participant
      Topics: 2
      Replies: 25
      Points: 167
      Helping Hand
      Rank: Participant

      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 🙂

       

    • #129942

      Moderator
      Topics: 8
      Replies: 739
      Points: 2,071
      Helping Hand
      Rank: Community Hero

      Good catch, I missed -Append

  • #130083

    Participant
    Topics: 1
    Replies: 1
    Points: -3
    Rank: Member

    Thank you so much, that worked.

The topic ‘Export Users Member of to Excel’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort