Powershell script has me stumped

Question

I have a script that was working jsut fine and now suddenly stopped working. The issue is that my script takes a spreadsheet and uses the email addresses on it to query AD and then find the users email, manager and manager email address. My script needs to be able to put blank liones in the output spreadsheet so that the output spreadsheet has the exact same number of lines as the input spreadsheet and everything is in the exact same order.

Currently if my script finds a user who has no manager listed it stops and will not run. How can I alter my script so that it is basically foolproof, and that if it cant find the necessary information it needs to put a 'NA' or 'Not Found' in the output spreadsheet.

#NOTE: Source file must have email as the column header for this script to work!!!
#Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
# Import the data from CSV file and assign it to variable
Import-Module ActiveDirectory
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$ErrorActionPreference = 'Stop'
$OpenFIleDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.Title = "Please Select a CSV File to process"
$OpenFileDialog.InitialDirectory = $InitialDirectory
$OpenFileDialog.Filter = "CSV (*.csv) | *.csv"
$OpenFileDialog.ShowDialog() | Out-Null
$Path = $OpenFileDialog.Filename
$user = Import-Csv -Path $Path
 Function Get-FileName($initialDirectory) {   
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |
    Out-Null

    $SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $SaveFileDialog.Title = "Where do you want to save the file?"
    $SaveFileDialog.initialDirectory = $initialDirectory
    $SaveFileDialog.filter = "CSV file (*.csv)|*.csv| All Files (*.*)|*.*";
    $SaveFileDialog.ShowDialog() | Out-Null
    $SaveFileDialog.filename
}
$SaveMyFile = Get-Filename

$params = @{
    Properties = 'mail', 'samaccountname', 'manager'
}

$object = {
    param(
        $mail,
        $samAccountName = 'Not Found',
        $manager        = 'Not Found',
        $managerEmail   = 'Not Found'
    )

    [pscustomobject]@{
        Mail           = $mail
        SamAccountName = $samAccountName
        Manager        = $manager
        ManagerEmail   = $managerEmail
    }
}

[System.Windows.Forms.MessageBox]::Show('Script Starting . . .','Begin Script')
$user | ForEach-Object {
    # if there is no value in this column for this object
    if([string]::IsNullOrWhiteSpace($_.email)) {
         #skip it, go next
        return
    }
    
    $params['Filter'] = "mail -eq '$($_.email)'"
    $aduser = Get-ADUser @params

    if(-not $aduser) {
        return & $object -Mail $_.email
    }

    $manager = $aduser.Manager | Get-ADUser -Properties mail
    & $object $aduser.Mail $aduser.SamAccountName $manager.Name $manager.mail
} | Export-CSV -Path $SaveMyFile -NoTypeInformation

[System.Windows.Forms.MessageBox]::Show('Script Complete.','Completed')

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 line:60 char:34 + $manager = $aduser.Manager | Get-ADUser -Properties mail — Chris Newton, Feb 22 '23 at 13:07
ok Setting the $ErrorActionPreference = 'Continue' basically fixed my issue, except that in the manager email column, when it cant find a manager it just puts a blank entry instead of the 'Not Found' that I wanted. This technically works for me but i would like to have it not show the error when it runs and instead puts the correct text in that column. — Chris Newton, Feb 22 '23 at 13:12

mklement0 · Answer 1 · 2023-02-22T13:59:52.893

if $aduser.Manager is $null, the following call predictably fails with the error message you cite ($null is not a valid identity to look up):

$manager = $aduser.Manager | Get-ADUser -Properties mail

The following should bypass the error:

$manager = if ($aduser.Manager) { $aduser.Manager | Get-ADUser -Properties mail }

If $aduser.Manager is $null (or the empty string), the Get-AdUser call is bypassed and $null (technically, an "Automation Null" value that behaves like $null in expression contexts) is stored in $manager.

Additionally, to get the desired default values in the [pscustomobject] you construct, you must add a conditional afterwards:

if ($manager) {
  & $object $aduser.Mail $aduser.SamAccountName $manager.Name $manager.mail
} else {
  & $object $aduser.Mail $aduser.SamAccountName
}

Note that both updated snippets take advantage of PowerShell's implicit to-Boolean coercion in conditionals, whose rules are summarized in the bottom section of this answer.

Thank you for that explanation! I did figure out the first part of what you said but the adding of conditionals adfter the [pscustomobject] was a line of thinking I had but couldnt figure out the powershell syntax. — Chris Newton, Feb 22 '23 at 13:55
well, it seems like it does however I got 2 managers it couldnt find and yet I cannto figure out why it even tried to search for them as none of the users in my spreadshet work for these managers. So aside from that anomaly, it worked perfectly and did solve my problem! — Chris Newton, Feb 22 '23 at 14:45

albvar · Accepted Answer · 2023-02-23T15:25:39.803

See below

#NOTE: Source file must have email as the column header for this script to work!!!
#Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
# Import the data from CSV file and assign it to variable
Import-Module ActiveDirectory
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
$ErrorActionPreference = 'Stop'
$OpenFIleDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.Title = "Please Select a CSV File to process"
$OpenFileDialog.InitialDirectory = $InitialDirectory
$OpenFileDialog.Filter = "CSV (*.csv) | *.csv"
$OpenFileDialog.ShowDialog() | Out-Null
$Path = $OpenFileDialog.Filename
$user = Import-Csv -Path $Path
 Function Get-FileName($initialDirectory) {   
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |
    Out-Null

    $SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $SaveFileDialog.Title = "Where do you want to save the file?"
    $SaveFileDialog.initialDirectory = $initialDirectory
    $SaveFileDialog.filter = "CSV file (*.csv)|*.csv| All Files (*.*)|*.*";
    $SaveFileDialog.ShowDialog() | Out-Null
    $SaveFileDialog.filename
}

$SaveMyFile = Get-Filename

[System.Windows.Forms.MessageBox]::Show('Script Starting . . .','Begin Script')
$user | ForEach-Object {
    # Create PSCustomObject to store the results
    $email = $null
    $email = $_.email.Trim()
    $result = [pscustomobject]@{
        Mail           = if ([string]::IsNullOrEmpty($email)) { 'Not Found in csv' } else { $email }
        SamAccountName = 'Not Found'
        Manager        = 'Not Found'
        ManagerEmail   = 'Not Found'
    }
    
    if ($result.Mail -ne 'Not Found in csv') {
        try {
            $adUser = $null
            $adUser = Get-AdUser -Filter ("mail -eq '$email'") -ErrorAction Stop -Properties 'mail', 'samaccountname', 'manager'
            $result.SamAccountName = $adUser.SamAccountName
            $result.Manager        = $adUser.Manager
            try {
                if ($adUser.Manager) {
                    $manager = $null
                    $manager = Get-AdUser -Identity $adUser.Manager -ErrorAction Stop -Properties 'mail'
                    $result.ManagerEmail = $manager.name
                }
                else {
                    $result.ManagerEmail = 'No Manager'
                }
            }
            catch {
                $result.ManagerEmail = "Issue retrieving manager email $($_.Exception.Message)" 
            }
        }
        catch {
            $result.Mail = "$email Not Found in AD"
        }
        finally {
            $result
        }
    }
    else {
        $result
    }
} | Export-CSV -Path $SaveMyFile -NoTypeInformation

[System.Windows.Forms.MessageBox]::Show('Script Complete.','Completed')

This is beautiful, however I do get an error that is saying that Flow of Control cannot leave a finally block. Not sure if that is due to my environment or whatnot. As you can see I am only a beginner scripter. — Chris Newton, Feb 22 '23 at 13:54
How do I get the manager names to show up witht heir friendly names instead of the CN? Ive tried a few variations and havent figured it out yet. — Chris Newton, Feb 22 '23 at 14:37
Updated to show the managers name, make sure to review answers and accept the one you end up using. — albvar, Feb 23 '23 at 15:16
I was wrong, this script doesnt work at all. Most of the error checking never actually fires off and it jsut doesnt work. Puts blank lines instead of the Not Found, any managers that are contacts in AD are not found, im scrapping this and going back to the drawing board. — Chris Newton, Mar 08 '23 at 13:30

Powershell script has me stumped

2 Answers2