Export Users Group Memberships to CSV

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Aapeli Hietikko Aapeli Hietikko 1 month ago.

  • Author
    Posts
  • #73517
    Profile photo of Brian Jacobsen
    Brian Jacobsen
    Participant

    I'm looking to create a script to solve an audit need where i can export all the users in a particular OU and the Groups they are a member of. I want to export this to a CSV file and have each group for the particular user display on a separate line like below. I have figured out how to do this and put all the group in one cell coma delimited but for audit reasons would need each group on a separate line. I'm stuck at this point. Sample script that i have so far below. Thanks

    ExcelColumnA ExcelColumnB ColumnC
    User Display Name1 Samaccountname GroupName
    User Display Name1 Samaccountname GroupName
    User Display Name1 Samaccountname GroupName
    User Display Name2 Samaccountname GroupName
    User Display Name2 Samaccountname GroupName
    User Display Name3 Samaccountname GroupName

    Current Script:
    #Import the AD module
    import-module ActiveDirectory

    #Set OU for script to search in
    $OU="OU=TestOU,DC=TestDC,DC=com"

    Get-ADUser -Filter * -Properties DisplayName,samaccountname,department,memberof -searchbase $OU | % {
    New-Object PSObject -Property @{
    UserName = $_.DisplayName
    SamAccountName = $_.samaccountname
    Department = $_.department
    Groups = ($_.memberof | Get-ADGroup | Select -ExpandProperty Name) -join ","
    }
    } | Select UserName,SamAccountName,Department,Groups | Export-Csv C:\testreport.csv -NTI

  • #73528
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    Could you please format your code as code? Thanks.

    $OU="OU=TestOU,DC=TestDC,DC=com"
    
    $List = Get-ADUser -Filter * -Properties DisplayName,samaccountname,department,memberof -searchbase $OU | 
        ForEach-Object {
            $User = $_
            $User.memberof | 
                ForEach-Object {
    	            [PSCustomObject]@{
    		            DisplayName = $User.DisplayName
    		            SamAccountName = $User.samaccountname
                        Department = $User.department
                        Group = Get-ADGroup -Identity $_ | Select-Object -ExpandProperty Name
    	            }  # end of PSCustomObject
                }  # end of Foreach-Object #2
        }  # end of Foreach-Object #1
    
    $List
    $List | Export-Csv -Path 'C:\testreport.csv' -NoTypeInformation -Encoding UTF8 
    • #73568
      Profile photo of Brian Jacobsen
      Brian Jacobsen
      Participant

      Apologies for not posting the code correctly and Thanks for the assistance. It worked perfectly to what i needed. Still a PowerShell newbie.

    • #73792
      Profile photo of Aapeli Hietikko
      Aapeli Hietikko
      Participant

      That's a beautiful one 🙂
      #thatsAkeeper

You must be logged in to reply to this topic.