Active Directory Export to CSV

PowerShell Help
1

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

2

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 '; ’

3

potential issue, names don’t have to match samaccountnames

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

4

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

5 
($_.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.

6

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",
7

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

8

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’”

9

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

11

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

12

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

Thanks a bunch!!!