# CSV of AD Memberships per Department

**URL:** <https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109>\
**Category:** PowerShell Help\
**Created:** [April 11, 2023, 11:47pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109 "2023-04-11T23:47:54Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![SelectStarDelete](https://avatars.discourse-cdn.com/v4/letter/s/ecccb3/32.png) [@SelectStarDelete](https://forums.powershell.org/u/SelectStarDelete)\
**Post date:** [April 11, 2023, 11:47pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/1 "2023-04-11T23:47:54Z")

</div>

OK, I thought this would be an easy one, but my PSfu is weak and I need some assistance.  
All I am trying to do is get a csv output of all our AD user’s departments and their corresponding group memberships.  
I know how to query users for all the departments, and I know how to query for each users group memberships. Im just getting stuck putting it all together.

Here is what I have so far.

’  
$OUpath = ‘OU=people,DC=domain,DC=com’  
$Departments = Get-ADUser -Filter \* -SearchBase $OUpath -Properties \* | Select-object UserPrincipleName,Department  
Get-adprincipalgroupmembership | Foreach-Object ($UserPrincipleName in $Departments)  
Export-csv c:\scripts\department\_memberships.csv  
’

The result I am trying to get is a table like this:

Department1 Department2 Department3  
Domain Users Domain Users Domain Users  
Group 1 Group 3 Group 2  
Group 2 Group 4 Group 4  
Group 3 Group 5 Group 6  
etc etc etc

Thanks in advance for the help!

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [April 12, 2023, 1:11am UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/2 "2023-04-12T01:11:58Z")

</div>

Michael,  
Welcome to the forum. 👋🏼

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( \</\> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

[How to format code in PowerShell.org](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/b/bee50c628238f2c076c0504efb6b15f2fb11f002.gif) 1 \<---- Click 👆🏽 😉

> [@SelectStarDelete](#):
>
> The result I am trying to get is a table like this:
> 
> Department1 Department2 Department3  
> Domain Users Domain Users Domain Users  
> Group 1 Group 3 Group 2  
> Group 2 Group 4 Group 4  
> Group 3 Group 5 Group 6  
> etc etc etc

That won’t work. You cannot use the values of one single property as table headers.

What for do you actually want to use the data you’re after?

> [@SelectStarDelete](#):
>
> $OUpath = ‘OU=people,DC=domain,DC=com’  
> $Departments = Get-ADUser -Filter \* -SearchBase $OUpath -Properties \* | Select-object UserPrincipleName,Department  
> Get-adprincipalgroupmembership | Foreach-Object ($UserPrincipleName in $Departments)  
> Export-csv c:\scripts\department\_memberships.csv

Did you write this code? Did you try to run this code?

And just to mention it at least once: Using `-Properties *`, although you only need the property `Department` besides the default return set, is not a good idea. This puts much more stress to your AD than necessary.

---

<div class="post-metadata">

**Author:** ![SelectStarDelete](https://avatars.discourse-cdn.com/v4/letter/s/ecccb3/32.png) [@SelectStarDelete](https://forums.powershell.org/u/SelectStarDelete)\
**Post date:** [April 12, 2023, 4:40pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/3 "2023-04-12T16:40:26Z")

</div>

Hi Olaf,  
Thanks for the tip on code formatting, but I actually do not see a \<\> button in the message editor.

The business case is that we don’t have a baseline membership list per department for when we create new users. Various people in the same department will have different group memberships. Its a mess. So, I am working on evaluating all the different departments to see what memberships they have and from that build a baseline group membership template for each department. Probably TMI, but you did ask.

The cmdlets for the variables work, but I dont think I have the foreach correct.  
Also, I cant remove -Properties \* because then the cmdlet will not be able to see the Department attribute

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [April 12, 2023, 5:55pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/4 "2023-04-12T17:55:35Z")

</div>

> [@SelectStarDelete](#):
>
> I actually do not see a \<\> button in the message editor.

![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/7/7f0bf96309264f7b9bc45b0476e87d1a3a30530b.png)

> [@SelectStarDelete](#):
>
> Probably TMI, but you did ask.

That’s OK. At least it tells me that you’re not bound to this particular format. 😉

> [@SelectStarDelete](#):
>
> The cmdlets for the variables work, but I dont think I have the foreach correct.

hmmm … no comment on that … 😏

> [@SelectStarDelete](#):
>
> I cant remove -Properties \* because then the cmdlet will not be able to see the Department attribute

Instead of `-Properties *` what queries ALL available attributes you should use `-Properties Department`. What’s way less stressful for your DC. 👆🏼

Here’s how I’d approach it. First we need the list of users with their departments and since we already query the users we can query their group memberships as well.

```auto
$SearchBase = 'OU=people,DC=domain,DC=com'
$UserList = Get-ADUser -Filter * -SearchBase $SearchBase -Properties MemberOF, Department

```

Since the MemberOf attribute contains a list of DistinguishedNames what’s ugly and hard to read I’d like to query the names of the groups. That’s easier on the eyes I think. And to be able to use this as a lookup table we save the output in a hashtable.

```auto
$ADGroupLookupTable = @{}
$UserList.MemberOf | 
Select-Object -Unique |
ForEach-Object {
   $ADGroupLookupTable.add( $_ , (Get-ADGroup -Identity $_).Name )
}

```

Now we use both lists and build our result list

```auto
$ResultList =
$UserList | 
ForEach-Object {
    foreach ($MemberShip in $_.MemberOf) {
        [PSCustomObject]@{
            UserName = $_.Name
            Department = $_.Department
            Group = $ADGroupLookupTable[$MemberShip]
        }
    }
}

```

Now you can group this list for the department and you have the info you wanted, right? 😉

```auto
$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name = $_.Name
        GroupList = $_.Group.Group -join ','
    }
}

```

---

<div class="post-metadata">

**Author:** ![SelectStarDelete](https://avatars.discourse-cdn.com/v4/letter/s/ecccb3/32.png) [@SelectStarDelete](https://forums.powershell.org/u/SelectStarDelete)\
**Post date:** [April 12, 2023, 6:17pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/5 "2023-04-12T18:17:51Z")

</div>

Thanks Olaf! This is great.  
Yeah I do not know why I cant see that button under the message editor. Permissions perhaps?

 ![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/b/bffb67e24847d9854a33112e66aa6e2fab102c09.png)

Thank you for all of the code! Incredibly helpful!  
When I run the entire script, it works, but displays the output in the PS window. I added an Export-CSV (with the path) to the end, but PS came back and reported this:

_cmdlet Export-Csv at command pipeline position 1_  
_Supply values for the following parameters:_  
_InputObject:_

I just pushed Enter, and it looks like it worked, however when I opened the CSV it says this:  
#TYPE System.String  
Length  
0

Is there a way I can export the results to csv so I can work with the data in Excel?

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [April 12, 2023, 6:43pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/6 "2023-04-12T18:43:31Z")

</div>

> [@SelectStarDelete](#):
>
> When I run the entire script, it works, but displays the output in the PS window. I added an Export-CSV (with the path) to the end, but PS came back and reported this:
> 
> _cmdlet Export-Csv at command pipeline position 1_  
> _Supply values for the following parameters:_  
> _InputObject:_
> 
> I just pushed Enter, and it looks like it worked, however when I opened the CSV it says this:  
> #TYPE System.String  
> Length  
> 0
> 
> Is there a way I can export the results to csv so I can work with the data in Excel?

Please do not describe the code - share it. If you get error messages you should share them completely - formatted as code as well.

You can format code manually by adding 3 consecutive backticks on two empty lines and the code in between … like this:

![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/f/f9f2478b57585666758eb95f38956c7c07f513a9.png)

---

<div class="post-metadata">

**Author:** ![SelectStarDelete](https://avatars.discourse-cdn.com/v4/letter/s/ecccb3/32.png) [@SelectStarDelete](https://forums.powershell.org/u/SelectStarDelete)\
**Post date:** [April 12, 2023, 7:57pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/7 "2023-04-12T19:57:28Z")

</div>

OK sure thing.

**Here is what I ran:**

```auto
PS C:\> $SearchBase = 'OU=People,DC=domain,DC=com'
$UserList = Get-ADUser -Filter * -SearchBase $SearchBase -Properties MemberOF, Department
$ExportPath = 'C:\Temp\department_memberships1.csv'

$ADGroupLookupTable = @{}
$UserList.MemberOf | 
Select-Object -Unique |
ForEach-Object {
   $ADGroupLookupTable.add( $_ , (Get-ADGroup -Identity $_).Name )
}

$ResultList =
$UserList | 
ForEach-Object {
    foreach ($MemberShip in $_.MemberOf) {
        [PSCustomObject]@{
            UserName = $_.Name
            Department = $_.Department
            Group = $ADGroupLookupTable[$MemberShip]
        }
    }
}

$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name = $_.Name
        GroupList = $_.Group.Group -join ','
    }
}
Export-Csv $ExportPath

```

**Here is the message I received:**

cmdlet Export-Csv at command pipeline position 1  
Supply values for the following parameters:  
InputObject: _(I pushed Enter here)_

**This is the result:**

Name GroupList

* * *

55,717,Sales Japan MS\_CMS,License-Networld,JPN\_Sales\_Assistant,JPN\_JMC,SP\_Legal\_Contracts,SP\_Legal\_Forms,All\_Tokyo\_Users…  
57,253,Customer Engineering - Japan PTKK-HitachiOmika,Lenovo\_Spec\_Acc,PTKK-TOSHIBA,PTKK-Panasonic,PTKK-NEC,PPMs\_Update\_Request,Lenovo\_Glo…  
99,911,Finance PTL-All,Accounting,Admin,Tokyo\_Users,allsubscribers6dfc3549,Legal,Americas\_Operations,SP\_Legal\_Contra…  
55,773,FAE Japan ServerTiger Team,ServerTigerTeam,Lenovo\_Global,JPSupport,JPN\_Managers,JPN\_JMC,Transition,SP\_Legal\_Con…  
PTL-All,NuBIOS,PTL-All,PTL-All,PTL-All,NuBIOS,PTL-All,allsubscribers6dfc3549,PTL Email Control Access…  
57,251,Customer Engineering - Korea Korea\_Users,All\_Korea\_Users,SCT4,SCT,PTL-All,KOR\_Users,Visual Studio Licensed Users,KOR\_Engs,Korea\_CE…  
55,774,FAE APAC Korea\_Users,NuBIOS-Integration,QA\_Korea,BP\_Group,SP\_Legal\_Contracts,SP\_Legal\_Forms,All\_Korea\_Users,SC…  
55,709,Sales APAC PTL-All,KOR\_Users,allsubscribers6dfc3549,SP\_Legal\_Contracts,SP\_Legal\_Forms,

_(this is a concatenated list as the actual list is very long)_

PS C:\>

**Here is also a screenshot of the results in ISE.**

 ![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/8/8d5702e05d5a1e083783e3e28dc76072fef0dcbf.png)

**This is what I got when I opened the exported csv file:**

![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/0/0da22a4fb02cf4ea1358ee206ab92676a0bd62ed.png)

Is this the format you were looking for? I think now I just need to be able to export the results to a CSV correctly.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [April 12, 2023, 8:34pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/8 "2023-04-12T20:34:31Z")

</div>

> [@SelectStarDelete](#):
>
> ```auto
> $ResultList |
> Group-Object -Property Department | 
> ForEach-Object {
> [PSCustomObject]@{
> Name = $_.Name
> GroupList = $_.Group.Group -join ','
> }
> }
> Export-Csv $ExportPath
> 
> ```

You have to provide something for `Export-Csv` to work with. Did you get the system of piping the results of one cmdlet to the next the PowerShell pipeline is based on? 🤨

Run it hits way

```auto
$ResultList |
Group-Object -Property Department | 
ForEach-Object {
    [PSCustomObject]@{
        Name = $_.Name
        GroupList = $_.Group.Group -join ','
    }
} |
Export-Csv $ExportPath

```

Your output looks weird though … the name column should have on one department per row.

---

<div class="post-metadata">

**Author:** ![SelectStarDelete](https://avatars.discourse-cdn.com/v4/letter/s/ecccb3/32.png) [@SelectStarDelete](https://forums.powershell.org/u/SelectStarDelete)\
**Post date:** [April 12, 2023, 9:04pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/9 "2023-04-12T21:04:46Z")

</div>

oh Jesus…I forgot to add the export path variable (slapping head).

So, like, it worked and I think I am good to go.

 ![image](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/1/18049b0787182f999643378f1c5940cfdc0f433a.png)

Thanks a ton for this. This was a tough one for me. I have learned a lot!

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:14pm UTC](https://forums.powershell.org/t/csv-of-ad-memberships-per-department/22109/10 "2024-05-16T20:14:57Z")

</div>