Export users list from OU in a csv file

Question

question

AllanSantos-8663 asked Oct 6, '20 AllanSantos-8663 commented Oct 7, '20

Export users list from OU in a csv file

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,

windows-server-powershell windows-active-directory

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2020-10-07T18:27:24.867Z

RichMatheisen-8856 answered Oct 7, '20 AllanSantos-8663 commented Oct 7, '20

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

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AllanSantos-8663 · Oct 07, 2020 at 07:23 PM

It worked like a charm!

Thank you very much for your assistance,

0 ·

RichMatheisen-8856 AllanSantos-8663 · Oct 07, 2020 at 07:30 PM

The one condition the script does not address is the remote possibility of the user not being a member of any groups. You can fix that by moving line #34 so it appears below line #35. And adding "$Props."Member Of" = ''" to line #30.

1 ·

AllanSantos-8663 RichMatheisen-8856 · Oct 07, 2020 at 07:38 PM

Thank you RIch.

Just for the record, the "Manager" is the only info missing in the CSV file (all cells are blank), but I can get this info iby the command below:

Get-ADUser -Filter * -SearchBase $DN -Properties name,manager |
Select-Object name,manager |
Sort-Object -Property name |
Format-Table -AutoSize

0 ·

Answer 2 · 2020-10-06T19:30:19.183Z

RichMatheisen-8856 answered Oct 6, '20 AllanSantos-8663 commented Oct 7, '20

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AllanSantos-8663 · Oct 07, 2020 at 02:43 PM

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 ·

Answer 3 · 2020-10-07T14:43:30.433Z

AllanSantos-8663 answered Oct 7, '20

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,

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2020-10-07T15:02:44.983Z

RichMatheisen-8856 answered Oct 7, '20 AllanSantos-8663 commented Oct 7, '20

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AllanSantos-8663 · Oct 07, 2020 at 04:57 PM

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.

30692-document.pdf

0 ·

document.pdf (65.6 KiB)

Answer 5 · 2020-10-07T15:41:09.46Z

AllanSantos-8663 answered Oct 7, '20

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.

document.pdf (65.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question