# Active Directory Export to CSV

**URL:** <https://forums.powershell.org/t/active-directory-export-to-csv/7081>\
**Category:** PowerShell Help\
**Created:** [August 26, 2016, 9:31am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081 "2016-08-26T09:31:53Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![nathan-whitehouse-2](https://avatars.discourse-cdn.com/v4/letter/n/f04885/32.png) [@nathan-whitehouse-2](https://forums.powershell.org/u/nathan-whitehouse-2)\
**Post date:** [August 26, 2016, 9:31am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/1 "2016-08-26T09:31:53Z")

</div>

Hello.  
I am working on a script to export info of user accounts for one of my clients from their AD.  
The hard part I am having is that I need to get the group memberships but just the Short Name for the groups and to have them separated by a “;”.

Here is the code I have thus far. I can get the group membership as a string but it is messing up the formatting of my CSV file.

```
$AllADUsers = Get-ADUser -server $ADServer `
-AuthType Basic `
-Credential $cred -searchbase $SearchBase `
-Filter * -Properties * | Where-Object {$_.info -NE 'Migrated'} | Where-Object {$_.Company -NE 'Company'}#ensures that updated users are never exported.

$AllADUsers |
Select-Object @{Label = "First Name";Expression = {$_.GivenName}},
@{Label = "Last Name";Expression = {$_.Surname}},
@{Label = "Display Name";Expression = {$_.DisplayName}},
@{Label = "Logon Name";Expression = {$_.sAMAccountName}},
@{Label = "Group Memberships";Expression = { $_.memberof | Out-String}},
@{Label = "Company";Expression = {$_.Company}},
@{Label = "Phone";Expression = {$_.telephoneNumber}},
@{Label = "Email";Expression = {$_.Mail}},
@{Label = "Account Status";Expression = {if (($_.Enabled -eq 'TRUE') ) {'Enabled'} Else {'Disabled'}}}, # the 'if statement# replaces $_.Enabled
@{Label = "Last LogOn Date";Expression = {$_.lastlogondate}} |
```

As always thank to the community for your help

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 9:38am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/2 "2016-08-26T09:38:44Z")

</div>

two options, you could split the strings or you could go back into ad and retrieve the names.

((get-aduser me -Properties memberof).memberof | % {(Get-ADObject $\_).name}) -join '; ’

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 9:41am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/3 "2016-08-26T09:41:21Z")

</div>

potential issue, names don’t have to match samaccountnames

((get-aduser me -Properties memberof).memberof | % {$\_.split(‘,’)[0] -replace ‘cn=’} ) -join '; ’

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 9:44am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/4 "2016-08-26T09:44:02Z")

</div>

btw, you only need a single expression for the new column. List the properties you want in the -properties parameter.

---

<div class="post-metadata">

**Author:** ![powershellish](https://avatars.discourse-cdn.com/v4/letter/p/b5ac83/32.png) [@powershellish](https://forums.powershell.org/u/powershellish)\
**Post date:** [August 26, 2016, 10:32am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/5 "2016-08-26T10:32:46Z")

</div>

```
($_.memberof | Get-ADGroup | Select-Object -ExpandProperty SamAccountName) -join ';'
```

That ought to do it. Now if it is a ton of users that will be a call to AD for every group for every user; that could be a big performance hit. There is a way you could get all the groups ahead of time and build a DN to sam hash table and use that to get some more performance out of it if its needed.

---

<div class="post-metadata">

**Author:** ![nathan-whitehouse-2](https://avatars.discourse-cdn.com/v4/letter/n/f04885/32.png) [@nathan-whitehouse-2](https://forums.powershell.org/u/nathan-whitehouse-2)\
**Post date:** [August 26, 2016, 11:47am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/6 "2016-08-26T11:47:34Z")

</div>

So I have tried all of the suggestions and I am not getting any group output.

Example:

```
$AllADUsers = Get-ADUser -server $ADServer `
-AuthType Basic `
-Credential $cred -searchbase $SearchBase `
-Filter * -Properties * | Where-Object {$_.info -NE 'Migrated'} | Where-Object {$_.Company -NE 'Fiserv'}#ensures that updated users are never exported.

$AllADUsers |
Select-Object @{Label = "First Name";Expression = {$_.GivenName}},
@{Label = "Last Name";Expression = {$_.Surname}},
@{Label = "Display Name";Expression = {$_.DisplayName}},
@{Label = "Logon Name";Expression = {$_.sAMAccountName}},
@{Label = "Group Memberships";Expression = { ($_.memberof | Get-ADGroup | Select-Object -ExpandProperty SamAccountName) -join ';'}},
#@{Label = "Full address";Expression = {$_.StreetAddress}},
#@{Label = "City";Expression = {$_.City}},
#@{Label = "State";Expression = {$_.st}},
#@{Label = "Post Code";Expression = {$_.PostalCode}},
#@{Label = "Country/Region";Expression = {if (($_.Country -eq 'GB') ) {'United Kingdom'} Else {''}}},
#@{Label = "Job Title";Expression = {$_.Title}},
@{Label = "Company";Expression = {$_.Company}},
#@{Label = "Directorate";Expression = {$_.Description}},
@{Label = "POD1 Tenant ID";Expression = {$_.Department}},
@{Label = "POD2 Tenant ID";Expression = {$_.physicalDeliveryOfficeName}},
@{Label = "Phone";Expression = {$_.telephoneNumber}},
@{Label = "Email";Expression = {$_.Mail}},
#@{Label = "Manager";Expression = {%{(Get-AdUser $_.Manager -server $ADServer -Properties DisplayName).DisplayName}}},
@{Label = "Account Status";Expression = {if (($_.Enabled -eq 'TRUE') ) {'Enabled'} Else {'Disabled'}}}, # the 'if statement# replaces $_.Enabled
@{Label = "Last LogOn Date";Expression = {$_.lastlogondate}} |
```

Output

```
"First Name","Last Name","Display Name","Logon Name","Group Memberships","Company","POD1 Tenant ID","POD2 Tenant ID","Phone","Email","Account Status","Last LogOn Date"
"Jane","Dow","Jane Dow","jane.dow","","Company","01",,"555-555-5555","Jane.Dow@company.com","Enabled",
```

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 12:31pm UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/7 "2016-08-26T12:31:54Z")

</div>

You must learn to think in objects. Remove all that select stuff, it’s already present in the objects you’re returning. Don’t use property \*

Work with one object until you get the output you want then expand your query. Only use the expression for the custom property.

$splat = @{properties = @(“mail”,“memberof”,“displayname”)}  
$user = get-aduser me @splat  
$user |select givenname,surname,mail,@{n=‘memberofjoined’;e={($_.memberof | % {$_.split(‘,’)[0] -replace ‘cn=’}) -join '; '}} -excludeproperty memberof

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 12:37pm UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/8 "2016-08-26T12:37:23Z")

</div>

Also don’t use the double where… put them together in the filter.

-filter {(info -NE ‘Migrated’) -and (Company -NE ‘Fiserv’)}

You can also write it like this. Personal pref…I like to stay consistent with the where and the filter syntax.

-Filter “givenname -eq ‘dan’ -and surname -eq ‘potter’”

---

<div class="post-metadata">

**Author:** ![dan-potter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/dan-potter/32/138_2.png) [@dan-potter](https://forums.powershell.org/u/dan-potter)\
**Post date:** [August 26, 2016, 12:56pm UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/9 "2016-08-26T12:56:19Z")

</div>

This might be easier to get started. Note how we don’t have to add the default properties after the properties parameter.

```
$AllADUsers = Get-ADUser -filter { (info -NE 'Migrated') -and (Company -NE 'Fiserv') } -Properties displayname, memberof

$AllADUsers | foreach {
	
	[pscustomobject]@{
		
		display = $_.displayname
		groups = ($_.memberof | % { $_.split(',')[0] -replace 'cn=' }) -join '; '
		sam = $_.samaccountname
		fn = $_.givenname
		ln = $_.surname
		
	}
	
	
}
```

---

<div class="post-metadata">

**Author:** ![powershellish](https://avatars.discourse-cdn.com/v4/letter/p/b5ac83/32.png) [@powershellish](https://forums.powershell.org/u/powershellish)\
**Post date:** [August 26, 2016, 6:44pm UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/10 "2016-08-26T18:44:23Z")

</div>

```
Get-ADUser -Filter * -Properties memberof |
Select-Object SamAccountName,@{
    Label = "Group Memberships"
    Expression = { 
        ( $_.memberof | 
          Get-ADGroup | 
          Select-Object -ExpandProperty SamAccountName
        ) -join ';'
    }
} |
ConvertTo-Csv -NoTypeInformation
```

I ran that and it worked for me.

---

<div class="post-metadata">

**Author:** ![nathan-whitehouse-2](https://avatars.discourse-cdn.com/v4/letter/n/f04885/32.png) [@nathan-whitehouse-2](https://forums.powershell.org/u/nathan-whitehouse-2)\
**Post date:** [August 30, 2016, 8:31am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/11 "2016-08-30T08:31:53Z")

</div>

Thanks Dan. That is working much better.  
I am trying to get that to export to a CSV file. Can you help me out on that?

Thanks!!!

---

<div class="post-metadata">

**Author:** ![nathan-whitehouse-2](https://avatars.discourse-cdn.com/v4/letter/n/f04885/32.png) [@nathan-whitehouse-2](https://forums.powershell.org/u/nathan-whitehouse-2)\
**Post date:** [August 30, 2016, 8:33am UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/12 "2016-08-30T08:33:16Z")

</div>

I am also trying to pull out the account status and having a little trouble.

Thanks a bunch!!!

---

<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:40pm UTC](https://forums.powershell.org/t/active-directory-export-to-csv/7081/13 "2024-05-16T20:40:39Z")

</div>

Closed on May 16, 2024, 8:40pm UTC