Adding a ROW for missing Attribute values to Export-CSV

Question

I using the following POWER SHELL script, to extract ( to csv ) managers name , from a "Manager" user attribute.

#This script, , Exports the Manager name of the employee`s in the TXT file.
# users.txt file - contains a simply list of user names ( samaccount-names )

Get-Content D:\powershell\permmisions\Users.txt | Foreach-Object {

Get-ADUser -Identity $_ -Properties Manager | Select-Object name, Manager | Export-Csv D:\Powershell\ADuserinformation\Export-Managers-of-specific-users.csv
-Append

}

The challenge i am facing, is when is on the exported CSV file, the list "SKIPS" blank value-fields,In case there is no manager set for the user. And a ROWS is not created , where MANAGER is missing.

What i would like to do , is the script to enter a charcter ( ~ ) for example, where, value is blank. That way , a row will be created for the blank MANAGER value, on the CSV file

Please help , Thanks all in advance.

mklement0 · Accepted Answer · 2020-07-01T16:12:09.660

2

^{Note: At least the Name property should exist on all AD users retrieved, so you would get a row even for users where Manager is empty, but with an empty Manager column. If you do need to deal with possibly not all users named in Users.txt actually existing, see Theo's helpful answer.}

The simplest approach is to use a calculated property:

Get-ADUser -Identity $_ -Properties Manager | 
  Select-Object Name,  @{ Name='Manager'; 
                          Expression={ if ($_.Manager) { $_.Manager } else { '~' } } }

Note:

It is common to abbreviate the key names of the hashtable that defines the calculated property to n and e.
The if statement takes advantage of the fact that an empty string (or $null) evaluates to $false in a Boolean context; for an overview of PowerShell's implicit to-Boolean conversion, see the bottom section of this answer.

In PowerShell [Core] 7.0 or above, you could additionally take advantage of the ternary operator (<condition> ? <valueIfTrue> : <valueIfFalse>) to further shorten the command:

# PSv7+
Get-ADUser -Identity $_ -Properties Manager | 
  Select-Object Name,  @{ n='Manager'; e={ $_.Manager ? $_.Manager : '~' } }

^{Note: If $_.Manager were to return $null rather than the empty string ('') if no manager is assigned, you could use ??, the PSv7+ null-coalescing operator instead: $_.Manager ?? '~'}

edited Jul 01 '20 at 16:12

answered Jul 01 '20 at 12:10

mklement0

382,024
64
607
775

Hi, that seemed to work , if the attribute is indeed empty, BUT I THINK I HAVE AN ISSUE , WHERE THE MANAGER IS THERE BUT IS DISABLED, CAN OVERRIDEN? Also can you tell me where should i put the Export-CSV , i gor error`s trying to place it myself. ? – sj1986 Jul 01 '20 at 16:04
@sj1986: Re disabled managers: please ask a _new_ question (feel free to ping me here once you have done so). Re `Export-Csv`: that's difficult to diagnose here (and it isn't the right place to do so), but make sure that the target directory for the output file already exists and that you have the right permissions to create a file there. – mklement0 Jul 01 '20 at 16:15
Thanks, Any idea how can i add a row, if one of the users, i have inserted in the original USERS.TXT file doesnt exists? instead of powersheel to skip to the next line? – sj1986 Jul 01 '20 at 16:54
@sj1986 You can adapt Theo's answer as follows: in the last `else` branch, add something like `[pscustomobject] @{ Name = $null; Manager = $null }` - this will output a row with both columns empty. – mklement0 Jul 01 '20 at 18:11

score 1 · Answer 2 · answered Jul 01 '20 at 12:22

Not concise at all, but this allows you to insert more properties of interest in your report, and does some error-checking if the user listed in your input file does not exist:

$report = foreach ($account in (Get-Content D:\powershell\permmisions\Users.txt)) {
    $user = Get-ADUser -Filter "SamAccountName -eq '$account'" -Properties Manager -ErrorAction SilentlyContinue
    if ($user) {
        if (!$user.Manager) { $mgr = '~' }
        else { 
            # the Manager property is the DistinghuishedName for the manager.
            # if you want that in your report, just do 
            $mgr = $user.Manager
            # if you want the Name for instance of that manager in your report,
            # comment out the above line and do this instead:
            # $mgr = (Get-ADUser -Identity $user.Manager).Name
        }
        # now output an object
        [PsCustomObject]@{
            UserName = $user.Name
            Manager  = $mgr
        }
    }
    else {
        Write-Warning "User '$account' does not exist"
    }

}

# output on screen
$report | Format-Table -AutoSize

# output to CSV file
$report | Export-Csv -Path 'D:\Powershell\ADuserinformation\Export-Managers-of-specific-users.csv' -NoTypeInformation

Adding a ROW for missing Attribute values to Export-CSV

2 Answers2