AD query in Powershell combine results in .CSV file

Question

I'm trying to run a script from PowerShell to get users from 2 different OUs. I'm trying to export this into a .CSV file. However, the script I'm using isn't combining both Ous into one file, im only getting 1 OUs data.

the script im using:

$users = @()
$OU1 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$OU2 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$users  = Get-ADUser -Filter * -Searchbase $OU1 -Properties *|
Select-Object name, userPrincipalName, accountExpires, distinguishedName, physicalDeliveryOfficeName | export-csv -path "c:\users\$env:USERNAME\desktop\2-OU-List.csv"
$users += Get-ADUser -Filter * -SearchBase $OU2 -Properties *|
Select-Object name, userPrincipalName, accountExpires, distinguishedName, physicalDeliveryOfficeName | export-csv -path "c:\users\$env:USERNAME\desktop\2-OU-List.csv"

now, when i take out the export option my script works in powershell but im trying to export it:

$users = @()
$OU1 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$OU2 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$users  = Get-ADUser -SearchBase $OU1
$users += Get-ADUser -SearchBase $OU2

If you looking for a side-by-side "combine" (based on e.g. the `Name`), you might use this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Get-ADUser -SearchBase $OU1 |Join (Get-ADUser -SearchBase $OU2) -on Name -Name OU1,OU2` — iRon, Nov 15 '21 at 08:31

zett42 · Accepted Answer · 2021-11-15T12:39:50.350

Export-Csv overwrites existing output file, unless parameter -Append is used. So you always end up with the output of the 2nd Get-ADUser query only.

Using parameter -Append you don't even need to store the Get-ADUser result into intermediate array $users, so the script will need less memory.

$OU1 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$OU2 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"

# Create a new, empty file (clear any existing file)
$file = New-Item "c:\users\$env:USERNAME\desktop\2-OU-List.csv" -Force

# Use parameter -Append to add to the existing CSV
Get-ADUser -Filter * -Searchbase $OU1 -Properties *|
    Select-Object name, userPrincipalName, accountExpires, distinguishedName, physicalDeliveryOfficeName | 
    export-csv -path $file.FullName -Append

# Use parameter -Append to add to the existing CSV
Get-ADUser -Filter * -SearchBase $OU2 -Properties *|
    Select-Object name, userPrincipalName, accountExpires, distinguishedName, physicalDeliveryOfficeName | 
    export-csv -path $file.FullName -Append

Note that for symmetry I have specified -Append for both Get-ADUser calls and use New-Item to clear output from any previous run. This makes the code easier to maintain, as you don't need to take care which Get-ADUser call is the first one, when you add more queries or delete existing ones in the future.

Another approach is to wrap both Get-ADUser calls into a script block to effectively append their output streams. An advantage is that we can remove some duplicate code.

$OU1 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"
$OU2 = "OU=Standard users,OU=US,DC=x1,DC=contoso,DC=com','OU=Standard users,OU=AS, DC=x1,DC=contoso,DC=com"

# Create a script block to append the output streams of both `Get-ADUser` calls.
& {
    Get-ADUser -Filter * -Searchbase $OU1 -Properties *
    Get-ADUser -Filter * -SearchBase $OU2 -Properties *

} | Select-Object name, userPrincipalName, accountExpires, distinguishedName, physicalDeliveryOfficeName | 
    Export-Csv -Path "c:\users\$env:USERNAME\desktop\2-OU-List.csv"

The line & { uses the call operator to immediately execute the following script block.

The last line ends the script block and pipes its output, which consists of the serially combined output of both Get-ADUser calls, to Select-Object and then to Export-Csv. There is no need for -Append as we write the file in one go.

Maybe worth mentioning that using `-Properties *` is very inefficient when you only want two extra (besides the default) properties in the output — Theo, Nov 15 '21 at 10:57

AD query in Powershell combine results in .CSV file

1 Answers1