powershellcsvactive-directoryou

Export CSV of AD SamAccountNames and Groups for every user in specific OU

I found a similar question here, but it doesn't quite fit my need and I am having trouble tweaking it to do so.

I need to create a .csv file of all users in a specific OU along with what their AD group membership is in the following format:

User, Group (This is a Header)
User1, Group1
User1, Group2
User1, Group3
User2, Group1
User3, Group1
User4, Group1
User4, Group2

I think this script gets me most of the way there:

$Users = Get-ADGroup -SearchBase "OU=OrgUnit1,OU=OrgUnit2,OU=OrgUnit3,DC=XXX,DC=LOCAL" -Filter * `
    | Get-ADGroupMember -Recursive `
    | ForEach-Object { Get-ADUser $_ –Properties MemberOf | Select SamAccountName, MemberOf; } `
    | Sort-Object SamAccountName
    | export-csv C:\Messaging\PowerShell\ADUsers\Test1.csv

The problem with this is two fold.

  1. I want to search on OU=OrgUnit1 without having to search on the full distinguished name, because the sub OU's aren't always the same.

  2. The .csv output has the full distinguished name of the AD Group and I need just the Name of the group with no qualifiers

Solution

  • Use Get-ADOrganizationalUnit to get the OU you want to search:

    $ou = Get-ADOrganizationalUnit -Filter "Name -eq 'OrgUnit1'"
Get-ADGroup -Filter * -SearchBase $ou | ...

    The memberOf property is a list of groups (or rather their distinguished names). To get the output you want you need to unroll and resolve the group names and create new custom objects with the desired properties:

    ... | ForEach-Object {
  $account = $_.SamAccountName
  $_.MemberOf | Get-ADGroup | ForEach-Object {
    New-Object -Type PSCustomObject -Property @{
      SamAccountName = $account
      Group          = $_.Name
    }
  }
} | ...

    Also, there's no point in assigning pipeline output to a variable ($Users) if at the end of that pipeline you export the output to a file.

    Modified code:

    $ou = Get-ADOrganizationalUnit -Filter "Name -eq 'OrgUnit1'"
Get-ADGroup -Filter * -SearchBase $ou |
  Get-ADGroupMember -Recursive |
  ForEach-Object { Get-ADUser $_ -Properties MemberOf; } |
  Sort-Object SamAccountName |
  ForEach-Object {
    $account = $_.SamAccountName
    $_.MemberOf | Get-ADGroup | ForEach-Object {
      New-Object -Type PSCustomObject -Property @{
        SamAccountName = $account
        Group          = $_.Name
      }
    }
  } | Export-Csv 'C:\Messaging\PowerShell\ADUsers\Test1.csv'