Active Directory Export to CSV

This topic contains 11 replies, has 3 voices, and was last updated by Profile photo of Nathan W Nathan W 3 months, 1 week ago.

  • Author
    Posts
  • #52101
    Profile photo of Nathan W
    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
    Profile photo of Dan Potter
    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
    Profile photo of Dan Potter
    Dan Potter
    Participant

    potential issue, names don't have to match samaccountnames

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

  • #52110
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #52123
    Profile photo of Craig Duff
    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 3 months, 2 weeks ago by Profile photo of Craig Duff Craig Duff.
  • #52135
    Profile photo of Nathan W
    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
    Profile photo of Dan Potter
    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
    Profile photo of Dan Potter
    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
    Profile photo of Dan Potter
    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
    Profile photo of Craig Duff
    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
    Profile photo of Nathan W
    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
    Profile photo of Nathan W
    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.