Net-Connection Test PowerShell

Question

I'm having trouble exporting this basic net-connection test into the Export-Excel (import-Excel) feature. I need to export the results of a net-connection test from the array that was created.

Each CSV serverlist has 2 columns server name and port

Here is the code:

Import-Csv -Path .\serverlist.csv -Delimiter "," | ForEach-Object {$_.servers       
    
    $status = Test-NetConnection -ComputerName $_.servers -Port $_.port 
    

    if ($status.TcpTestSucceeded -eq $True) {
        $result = "ONLINE"
        $onlineCount ++    }

    elseif ($status.TcpTestSucceeded -eq $false) {
        $result = "OFFLINE"
        $offlineCount ++    }
    else {
        $result = "ERROR"
    }

   $result = [PSCustomObject]@{
                    ComputerName = $status.ComputerName
                    Port         = $status.RemotePort
                    Status       = $result
                    Date         = Get-Date -Format "dd/MM/yyyy"
                    Time         = Get-Date -Format "HH:mm"
                }    


    } $result | Export-Excel -Path ".\netStatus.xlsx" -AutoSize -AutoFilter -WorksheetName Net_Test 

    ii ".\netStatus.xlsx"

This is the Excel result. See there is only one column with addresses. I want : ComputerName, Port, Status, Date, Time

I wanted the $result array to show in excel similar to this:

I also get this error:

Answer 1

I think your issue is related to where the final $result is listed. Re-created your script and even without outputting to Excel (so just to screen) if I have it as

} $result

I get that same error. but if I change it to

}
$result

then it runs fine... except it doesn't do what you're after as it will only outout the last result as you're calling it from outside the ForEach loop. So it actually needs to be

   $result
}

So each iteration of the ForEach-Object loop it will output / export the results of that command.

The following code with my suggested changes mentioned above works and does what you're looking for.

Import-Csv -Path c:\batchscripts\serverlist.csv -Delimiter "," | ForEach-Object { $_.Servers    
    $status = Test-NetConnection -ComputerName $_.servers -Port $_.port 

    if ($status.TcpTestSucceeded -eq $True) {
        $result = "ONLINE"
        $onlineCount ++    }

    elseif ($status.TcpTestSucceeded -eq $false) {
        $result = "OFFLINE"
        $offlineCount ++    }
    else {
        $result = "ERROR"
    }
   
    $result = [PSCustomObject]@{
        ComputerName = $status.ComputerName
        Port         = $status.RemotePort
        Status       = $result
        Date         = Get-Date -Format "dd/MM/yyyy"
        Time         = Get-Date -Format "HH:mm"
    } 
    $result | export-csv -Path c:\batchscripts\pingoutput.csv -Append         
}

Only difference is I've outputted to .csv rather than .xlsx as I don't have Excel on this machine to test that part.

but with a serverlist.csv containing

Servers,Port
www.microsoft.com,80
www.amazon.com,80

I get an output of

#TYPE System.Management.Automation.PSCustomObject
"ComputerName","Port","Status","Date","Time"
"www.microsoft.com","80","ONLINE","17/05/2023","06:00"
"www.amazon.com","80","ONLINE","17/05/2023","06:00"

Answer 2

Something like this should work. I converted to csv because I am not sure what the Export-Excel is. The main problem you have with your code is that you are only going to get the last result because the last $result that you put, and the remainingScripts error you are getting is because you need to specify -Begin -Process and -End scriptblocks for the ForEach-Object. If you are trying to Pipe '|' into the Export-Excel you need to remove the $result right before the Export-Excel. You need to use Write-Output to output your data to the Pipe.

$csv = Import-Csv -Path .\serverlist.csv -Delimiter "," | ForEach-Object -Begin {} -Process {
    # Do this so it doesn't get confused for output by PowerShell. 
    # I am assuming this is for visual only.
    Write-Host ($_.servers)       
    
    $status = Test-NetConnection -ComputerName $_.servers -Port $_.port 
    

    if ($status.TcpTestSucceeded -eq $True) {
        $result = "ONLINE"
        $onlineCount ++    }

    elseif ($status.TcpTestSucceeded -eq $false) {
        $result = "OFFLINE"
        $offlineCount ++    }
    else {
        $result = "ERROR"
    }

   Write-Output ([PSCustomObject]@{
                    ComputerName = $status.ComputerName
                    Port         = $status.RemotePort
                    Status       = $result
                    Date         = Get-Date -Format "dd/MM/yyyy"
                    Time         = Get-Date -Format "HH:mm"
                })   


    } -End {} | ConvertTo-Csv -NoTypeInformation
    # This is just to write the result to the host. It can be removed.
    $csv