Import a CSV, run port connection, then export to new csv-CodePudding

I have a Sources.csv which has columns "Source," "Host" and "Port" (Source is just the name of the device I'm trying to connect to). My goal is to go line-by-line through this CSV, run a connection test to the IP and Port, then export to a new csv file to include these same columns as well as a new column "Reachable." This is what I have so far, but my csv file is filling up with all sorts of information that doesn't seem to make sense... So I'm curious if anyone can point me in the right direction:

$path = '.\Sources.csv'
$csv = Import-Csv -Path $path
Foreach($line in $csv){
    $TCPTest = Test-NetConnection $line.host -Port $line.port
    $Test = @{DataSource=$line.source; IP=$line.host; Port=$line.port; 
        Reachable=$TCPTest.TcpTestSucceeded}
    $Test | Export-Csv -Path .\SourceChecks.csv -append
    }

CodePudding user response：

You can handle this using calculated properties with Select-Object:

$path = '.\Sources.csv'
Import-Csv -Path $path | Select-Object *, @{
    Name       = 'Reachable'
    Expression = {Test-NetConnection $_.Host -Port $_.Port -InformationLevel Quiet}
} | Export-Csv newpathtocsv.csv -NoTypeInformation

Using -InformationLevel Quiet on Test-NetConnection so the cmdlet returns a boolean:

If you set this parameter to Quiet, the cmdlet returns basic information. For example, for a ping test, this cmdlet returns a Boolean value that indicates whether the attempt to ping a host or port is successful.

You can also add -WarningAction Ignore if you wish to not see the warning messages displayed to the console.

It's worth noting that Test-NetConnection is quite slow if you have many target hosts and ports to test connectivity. If you're looking to speed up this process you can give this function a try. It leverages the async capabilities from the .NET TcpClient Class.

An example, using the following CSV as input:

Host,Port
google.com,80
google.com,8080
cisco.com,80
cisco.com,443
amazon.com,80
amazon.com,389
amazon.com,636

Output would become:

PS /> Import-Csv $path | Test-TCPConnectionAsync -TimeOut 10

Source             Destionation Port Success
------             ------------ ---- -------
sourceComputerName google.com     80    True
sourceComputerName cisco.com     443    True
sourceComputerName cisco.com      80    True
sourceComputerName amazon.com     80    True
sourceComputerName google.com   8080   False
sourceComputerName amazon.com    389   False
sourceComputerName amazon.com    636   False

CodePudding user response：

Santango's answer works all the way back to PowerShell version 1 (I think).

If you can accept a dependency on PowerShell Version 3 (released in 2012), there is another option:

$sourcesPath = '.\Sources.csv'

$sources = Import-Csv $sourcesPath

$sources | ForEach {
    $reachable = Test-NetConnection $_.host -Port $_.port -InformationLevel Quiet

    [PSCustomObject]@{
        Source = $_.Source
        Host = $_.Host
        Port = $_.Port
        Reachable = $reachable
    }
} | 
Export-Csv newpathtocsv.csv