How to export result into the same excel sheet?-CodePudding

I have to extract active accounts whose attribute is empty in 3 organizational units. However, I would like the results to be displayed in the same excel file. Is it possible to help me with this?

$date = Get-Date -Format yyyy-MM-dd
$path = "c:\temp\empty_attributes.xlsx"

$OU1 = "OU=Accounts,DC=com,DC=contoso"
$OU2 = "OU=TestAccounts,DC=com,DC=contoso"
$OU3 = "OU=Users,DC=com,DC=contoso"

Get-ADUser -Filter {-not(employeeNumber -like "*") -and (Enabled -eq "True") }  -SearchBase $OU1 -Properties * | select name, samaccountname, employeeNumber | Export-excel -Path $path -WorksheetName $date -AutoSize -AutoFilter -TableStyle Medium2


Get-ADUser -Filter {-not(employeeNumber -like "*") -and (Enabled -eq "True") }  -SearchBase $OU2 -Properties * | select name, samaccountname, employeeNumber | Export-excel -Path $path -WorksheetName $date -AutoSize -AutoFilter -TableStyle Medium2


Get-ADUser -Filter {-not(employeeNumber -like "*") -and (Enabled -eq "True") }  -SearchBase $OU3 -Properties * | select name, samaccountname, employeeNumber | Export-excel -Path $path -WorksheetName $date -AutoSize -AutoFilter -TableStyle Medium2

CodePudding user response：

Just process it in one pipeline, instead of having multiple variables for each Organizational Unit, store them all in one array and then loop over it:

$date = Get-Date -Format yyyy-MM-dd
$path = "c:\temp\empty_attributes.xlsx"

$OUs = @(
    "OU=Accounts,DC=com,DC=contoso"
    "OU=TestAccounts,DC=com,DC=contoso"
    "OU=Users,DC=com,DC=contoso"
)

$params = @{
    LDAPFilter = '(&(!userAccountControl:1.2.840.113556.1.4.803:=2)(!employeeNumber=*))'
    Properties = 'employeeNumber'
}

$OUs | ForEach-Object { Get-ADUser @params -SearchBase $_ } |
    Select-Object name, samaccountname, employeeNumber |
    Export-excel -Path $path -WorksheetName $date -AutoSize -AutoFilter -TableStyle Medium2

You also need to consider stop using -Properties * and use only specific attributes you need to query, otherwise your query will be very inefficient.