Export users list from OU in a csv file

Allan Santos 21 Reputation points
2020-10-06T15:03:50.313+00:00

Hi team,

I`m currently working on a Powershell script in order to get the following information from all users in a specific OU and export them into a csv file:

  • givenname
  • surname
  • samaccountname
  • E-mail address
  • Departament
  • Title
  • manager
  • Account Status
  • member of
  • Office

Somehow this code does not work to get the info Office, Title, Manager and Department.

Could you please give me an assistance on this?

Import-Module -Name ActiveDirectory

$UserArray = @()
$Allusers = Get-ADUser -filter * -SearchBase 'OU=Brasilien,OU=Others,OU=Users,OU=Leschaco,DC=hq,DC=LESCHACO,DC=org' #usuários BR
$date = Get-Date -Format yyyyMMdd
foreach($user in $Allusers){

$userGroups = Get-ADPrincipalGroupMembership -Identity $user.samaccountname | select -ExpandProperty Name
foreach($userGroup in $userGroups){

    $userObject = New-Object -TypeName System.Object
    $userObject | Add-Member -NotePropertyName "Nome" -NotePropertyValue $user.givenName
    $userObject | Add-Member -NotePropertyName "Sobrenome" -NotePropertyValue $user.Surname
    $userObject | Add-Member -NotePropertyName "Citrix ID" -NotePropertyValue $user.SamAccountName
    $userObject | Add-Member -NotePropertyName "E-mail" -NotePropertyValue $user.UserPrincipalName
    $userObject | Add-Member -NotePropertyName "Departamento" -NotePropertyValue 
    $userObject | Add-Member -NotePropertyName "Cargo" -NotePropertyValue $user.title #Corrigir isso
    $userObject | Add-Member -NotePropertyName "Gerente Direto" -NotePropertyValue $user.manager #Corrigir isso
    $userObject | Add-Member -NotePropertyName "Status Conta" -NotePropertyValue $user.Enabled
    $userObject | Add-Member -NotePropertyName "Member Of" -NotePropertyValue $userGroup

    $UserArray +=$userObject

}

}

Get-ADUser -Filter * -SearchBase 'OU=Brasilien,OU=Others,OU=Users,OU=Leschaco,DC=hq,DC=LESCHACO,DC=org' -Properties name,memberof | Select name,memberof | Sort-Object -Property name | Format-Table -AutoSize

Exporta os resultados para o caminho I:\Brasil\IT\Controles\Scripts\Active Users BR\resultado.csv

$UserArray | Export-Csv -Path "I:\Brasil\IT\Controles\Scripts\Active Users BR\resultado2.csv" -NoTypeInformation

Best regards,

Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
6,245 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,462 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 45,906 Reputation points
    2020-10-07T18:27:24.867+00:00

    The duplicate column was just a name missing a space between "Gerente" and "Sireto" in the hash.

    Try this version (slightly modified). If you're still not getting the manager's display name you'll have to have a look at the object returned by the Get-ADUser to be sure that "manager" is the property you're looking for. It should contain the distinguishedName of the user's manager.

    Yes, you can combine the names. But I'll leave that up to you. You can use either the "format" operator ("-f") or simple string concatenation to do it.

    $DN = 'OU=Brasilien,OU=Others,OU=Users,OU=Leschaco,DC=hq,DC=LESCHACO,DC=org' #usuários BR
    
    $Props = [ordered]@{
        Nome = ""
        Sobrenome = ""
        'Citrix ID' = ""
        'E-mail' = ""
        Departamento = ""
        Cargo = ""
        'Gerente Sireto' = ""
        'Status Conta' = ""
        'Member Of' = ""
    }
    
    Get-ADUser -filter * -SearchBase $DN -Property Department, Title, Manager |
        ForEach-Object{
            $Props.Nome = $_.givenName
            $Props.Sobrenome = $_.Surname
            $Props."Citrix ID" = $_.SamAccountName
            $Props."E-mail" = $_.UserPrincipalName
            $Props.Departamento = ($_| Select-Object -Expand Department)
            $Props.Cargo = ($_ | Select-Object -Expand title) #Corrigir isso
            Try{
                $Props."Gerente Direto" = (Get-ADUser -Identity $_.manager -ErrorAction STOP | Select-Object -Expand DisplayName) #Corrigir isso
            }
            Catch{
                $Props."Gerente Direto" = ""
            }
            $Props."Status Conta" = $_.Enabled
    
            Get-ADPrincipalGroupMembership -Identity $_.samaccountname |
                ForEach-Object{
                    $Props."Member Of" = ($_ | Select-Object -ExpandProperty Name)
                    [PSCustomObject]$Props
                }
        } | Export-CSV c:\temp\UserAndGroup.csv -NoTypeInformation
    
    Get-ADUser -Filter * -SearchBase $DN -Properties name,memberof | 
        Select-Object name,memberof | 
            Sort-Object -Property name | 
                Format-Table -AutoSize
    
    1 person found this answer helpful.

4 additional answers

Sort by: Most helpful
  1. Allan Santos 21 Reputation points
    2020-10-07T15:41:09.46+00:00

    30705-document.pdf

    I ran the script again and the "Manager" is still not visible on the .csv file.

    I'm attaching some screenshots to share with you this data is populated in Active Directory.

    By the way, is it possible to make some more adjustments as per described below?

    • The columns B and D looks to be the same data but there are data on column D only. Can you please delete the column B?
    • Is it possible to merge the Surname and Givenname into a single cell? (Instead of one column for Allan and other for Santos, the idea would be to merge them into one cell to Allan Santos)

    Thanks again for the prompt reply.

    0 comments No comments

  2. Rich Matheisen 45,906 Reputation points
    2020-10-06T19:30:19.183+00:00

    See if this works for you:

    $DN = 'OU=Brasilien,OU=Others,OU=Users,OU=Leschaco,DC=hq,DC=LESCHACO,DC=org' #usuários BR
    
    $Props = @{
        Nome = ""
        Sobrenome = ""
        'Citrix ID' = ""
        'E-mail' = ""
        Departamento = ""
        Cargo = ""
        'Gerente Sireto' = ""
        'StatusConta' = ""
        'Member Of' = ""
    }
    
    Get-ADUser -filter * -SearchBase $DN -Property Department, Title, Manager |
        ForEach-Object{
            $user = $_
            Get-ADPrincipalGroupMembership -Identity $user.samaccountname |
                ForEach-Object{
                    $Props.Nome = $user.givenName
                    $Props.Sobrenome = $user.Surname
                    $Props."Citrix ID" = $user.SamAccountName
                    $Props."E-mail" = $user.UserPrincipalName
                    $Props.Departamento = $user.Department
                    $Props.Cargo = $user.title #Corrigir isso
                    $Props."Gerente Direto" = (Get-ADUser -Identity $user.manager).DisplayName #Corrigir isso
                    $Props."Status Conta" = $user.Enabled
                    $Props."Member Of" = ($_ | Select-Object -ExpandProperty Name)
                    [PSCustomObject]$Props
                }
        } | Export-CSV c:\temp\UserAndGroup.csv -NoTypeInformation
    
    Get-ADUser -Filter * -SearchBase $DN -Properties name,memberof | 
        Select name,memberof | 
            Sort-Object -Property name | 
                Format-Table -AutoSize
    

  3. Allan Santos 21 Reputation points
    2020-10-07T14:43:30.433+00:00

    Hello RichMatheisen-8856,

    I have tested the script and the console returned the following error message:

    ****Get-ADUser : Cannot validate argument on parameter 'Identity'. The argument is null. Provide a valid value for the argument, and then try running the command again.
    At C:\Users\santo00a\Downloads\microsoft_technet.ps1:26 char:66

    • ... $Props."Gerente Direto" = (Get-ADUser -Identity $user.manager).Displa ...
    • ~~~~~~~~~~~~~
    • CategoryInfo : InvalidData: (:) [Get-ADUser], ParameterBindingValidationException
    • FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.ActiveDirectory.Management.Commands.GetADUser****

    Besides, the csv file was created with the following missing data:

    • Department
    • Manager
    • User Title
    • Office

    Could you please give me an assistance with that?

    Thanks in advance.

    Regards,

    0 comments No comments

  4. Rich Matheisen 45,906 Reputation points
    2020-10-07T15:02:44.983+00:00

    I didn't write any data validation checks into the script. If the user has no manager the Get-ADUser will fail.

    Replace line 27 with this:

    $Props."Gerente Direto" = $manager
    

    Replace line 26 with this:

    if ($user.manager){
           $manager = (Get-ADUser -Identity $user.manager).DisplayName #Corrigir isso
    }
    else{
           $manager = ""
     }
    

    When you say that the CSV is missing data, do you mean the columns are missing, or that the columns aren't populated with data? If the data are missing are you sure that the users actually have information in those properties?

    If the properties are populated on the users it may be necessary to use code like this:

    $Props.Departamento = $user | Select -Expand Department