Export users list from OU in a csv file

Question

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,

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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,

Answer 5

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