How to get PowerShell to export error message to CSV?

Question

I'm trying to modify a PowerShell script to append errors in a CSV file. A former coworker had created a script to take computer names from a txt file, go through Active Directory, and create a CSV file (creates an Excel file, if that helps) with the resulting divisions and canonical names. The CSV file has three columns; "Name", which holds the names directly from the txt file; "Division" (pulled from "name" in AD) which holds the name of the division/department the system is part of; and "CanonicalName", which holds the full Canonical Name of each system as displayed in AD. Sometimes a computer will not have an entry in AD, and it will throw an error message into the host and that system will be missing from the CSV file. Below is the modified script, and for privacy reasons the file path will read "File Path" and show no actual file path.

Here's what I currently have:

$ComputerList = Get-Content "File Path"
$Date = Get-Date -Format dd-MM-yyyy

foreach ($Computer in $ComputerList)
{
    if (@(Get-ADComputer $Computer -ErrorAction SilentlyContinue).Count) #Check if computer exists in AD
    {
       Get-ADComputer -Identity $Computer -Properties * | Select Name, @{n='Division'; e={([string]$_.o)}}, CanonicalName |
       Export-Csv "File Path" -NoTypeInformation -Append
    }
    else #Annotate if computer does not exist in AD
    {
       $NoComputer = [PSCustomObject]
       @{
          Name = $Computer
          Division = "Object Not Found"
          CanonicalName = " "
       }
       
       $CSVData = $NoComputer | ConvertTo-Csv -NoTypeInformation
       $CSVData | Select-Object -Skip 1 |
       Export-Csv "File Path" -NoTypeInformation -Append #<-- Error happens on this line, according to host
    }
}

When running the script as shown above, it still throws the error message into the host, along with two columns for each missing systems that appear as follows (actual computer name not shown for privacy reasons):

Name                           Value                                                                                                                                                          
----                           -----                                                                                                                                                          
Name                           ComputerName                                                                                                                  
CanonicalName                                                                                                                                            
Division                       Object Not Found

It also throws the following error message in the host:

Export-Csv : Cannot append CSV content to the following file: File Path\Computer 
Identity List.csv. The appended object does not have a property that corresponds to the following column: Name. To continue with mismatched properties, add the -Force parameter, and then 
retry the command.
At File Path.ps1:36 char:8
+        Export-Csv File Path
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (Name:String) [Export-Csv], InvalidOperationException
    + FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.PowerShell.Commands.ExportCsvCommand

It then leaves out that system in the CSV file, when I want it to list the system and append that it couldn't find the system with "Object Not Found" in the "Division" column of the CSV file. I tried doing -Force, but it just creates a blank row instead. Based on what I've looked up, this should be working, but clearly I'm missing something. What do I need to change in order to make this work?

Answer 1

Change your logic to use a try / catch instead of an if / else, Get-ADComputer will not respect the the -ErrorAction unless you pipe input to it. You will also benefit your script's performance by querying only those attributes of interest (o and canonicalName) instead of all attributes (*), and also by constructing your output before exporting it instead of appending to it.

You also have a typo in your [PSCustomObject] line, the hashtable (@{ ... }) must be in the same line as the type accelerator and this is causing the property mismatch error when you export them to Csv, the variable $NoComputer is getting assigned the type of PSObject instead of your actual object.

$result = foreach ($Computer in $ComputerList) {
    $object = [pscustomobject]@{
        Computer      = $computer
        Division      = ''
        CanonicalName = ''
    }

    try {
        $adcomputer = Get-ADComputer $Computer -Properties o, canonicalName
        $object.Division = [string] $adcomputer.o
        $object.CanonicalName = $adcomputer.CanonicalName
    }
    catch {
        $object.Division = 'Object Not Found'
        $object.CanonicalName = ' '
    }

    $object
}

$result | Export-Csv 'File Path' -NoTypeInformation