Active Directory Export to CSV

This topic contains 11 replies, has 3 voices, and was last updated by  Nathan W 1 year, 2 months ago.

  • Author
    Posts
  • #52101

    Nathan W
    Participant

    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

  • #52106

    Dan Potter
    Participant

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

  • #52108

    Dan Potter
    Participant

    potential issue, names don't have to match samaccountnames

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

  • #52110

    Dan Potter
    Participant

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

  • #52123

    Craig Duff
    Participant
    ($_.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.

    • This reply was modified 1 year, 2 months ago by  Craig Duff.
  • #52135

    Nathan W
    Participant

    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",
    
  • #52144

    Dan Potter
    Participant

    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

  • #52148

    Dan Potter
    Participant

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

  • #52150

    Dan Potter
    Participant

    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
    		
    	}
    	
    	
    }
    
    
  • #52195

    Craig Duff
    Participant
    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.

  • #52501

    Nathan W
    Participant

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

  • #52503

    Nathan W
    Participant

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

    Thanks a bunch!!!!!!!!

You must be logged in to reply to this topic.