Hello,

I’m quite new to scripting. I have a need to parse some excel files to extract destination IPs and port info. My aim is then to generate PowerShell scripts which will loop through these and output the result of Test-NetConnection.

The purpose of this is not how I extract the data, is how the actual PowerShell script works.

I’ve made some progress with this script:

$array1 = “192.212.1.30”, 1433 $array2 = “192.213.1.30”, 1433

$array =$array1, $array2 foreach ($value in $array) { $currentTime = Get-Date -format “dd-MMM-yyyy HH:mm:ss”
$address = (Test-Connection -ComputerName (hostname) -Count 1).IPV4Address.IPAddressToString $results = Test-NetConnection -ComputerName $value[0] -Port$value[1] |
Select-Object @{ Name = “Date Tested” ; Expression= { $currentTime } }, @{ IP = “Source IP” ; Expression= {$address } }, ComputerName, RemotePort, RemoteAddress, TcpTestSucceeded | Format-Table -Autosize | Out-File -Append C:\Dev\process.txt

}

The first issue is that the IP address field does not display within the output. The second issue is that for every time round the loop the column headers get added. Is there any way I can stop this happening?

Thanks,

Joe.

You do not want to mix Format-* cmdlets with outputting to file. Those are designed to output to the console. Also the calculated properties should be name/expression or label/expression. Your example code ends with error that IP key is not valid.

Thank you for the reply. I’ve changed the code slightly, now have this:

$array1 = “192.212.1.30”, 1433 $array2 = “192.213.1.30”, 1433

$array =$array1, $array2 $MasterList = @()
foreach ($value in$array) {

$currentTime = Get-Date -format “dd-MMM-yyyy HH:mm:ss” $address = (Test-Connection -ComputerName (hostname) -Count 1).IPV4Address.IPAddressToString
$results = Test-NetConnection -ComputerName$value[0] -Port $value[1] $MyObject = New-Object PSObject -Property @{
DateTested = $currentTime Source =$address
Destination = $results.RemoteAddress RemotePort =$results.RemotePort
TcpTestSucceeded = $results.TcpTestSucceeded } $MasterList += $MyObject } $MasterList | Format-Table -Autosize | Out-File -Append C:\Dev\newResults.txt

This seems to work, minor point that the order of the properties of the custom object is not the same as specifed here. Read that you can use [ordered] but I couldn’t get that to work.

And if you’re wanting to output header row followed by data you’d either need to do it manually by writing the headers then the data, or probably better to use Export-Csv. If you use a Foreach-Object pipeline you can export it all at the end without the need to -Append. Finally, Set-Content can save you some headaches with different encodings and is generally preferred to Out-File

$array1 = "192.212.1.30", 1433$array2 = "192.213.1.30", 1433

$array1,$array2 | ForEach-Object {

$currentTime = Get-Date -format "dd-MMM-yyyy HH:mm:ss"$address = (Test-Connection -ComputerName (hostname) -Count 1).IPV4Address.IPAddressToString
Test-NetConnection -ComputerName $_[0] -Port$_[1] |
Select-Object @{ Name = "Date Tested" ; Expression= { $currentTime } }, @{ name = "Source IP" ; Expression= {$address } }, ComputerName, RemotePort, RemoteAddress, TcpTestSucceeded

} | Export-Csv C:\dev\process.txt -NoTypeInformation

An alternative to select-object is to create your own custom object. The properties will retain the order.

$array1 = "192.212.1.30", 1433$array2 = "192.213.1.30", 1433

$array1,$array2 | ForEach-Object {

$currentTime = Get-Date -format "dd-MMM-yyyy HH:mm:ss"$address = (Test-Connection -ComputerName (hostname) -Count 1).IPV4Address.IPAddressToString
Test-NetConnection -ComputerName $_[0] -Port$_[1] | Foreach-Object {
[PSCustomObject]@{
"Date Tested"    = $currentTime "Source IP" =$address
ComputerName     = $_.computername RemotePort =$_.remoteport
RemoteAddress    = $_.remoteaddress TcpTestSucceeded =$_.tcptestsucceeded
}
}
} | Export-Csv C:\dev\process.txt -NoTypeInformation

Just picked this back up. I’ve noticed a problem that the above code does not work when there is only a single item in the array, which I believe is down to the ForEach-Object command.

Here is an example of what fails:

$array1 = “192.212.1.30”, 1433 $array1| ForEach-Object {

$currentTime = Get-Date -format “dd-MMM-yyyy HH:mm:ss” $address = (Test-Connection -ComputerName (hostname) -Count 1).IPV4Address.IPAddressToString
Test-NetConnection -ComputerName $[0] -Port$[1] | Foreach-Object {
[PSCustomObject]@{
“Date Tested”= $currentTime “Source IP”=$address
ComputerName= $.computername RemotePort=$
.remoteport
RemoteAddress= $.remoteaddress TcpTestSucceeded =$
.tcptestsucceeded
}
}
} | Export-Csv C:\dev\process.txt -NoTypeInformation

Any suggestions appreciated.

Looks like your code is relying on having an array or arrays. It looks a little ugly, but you can force it like this:

$array1 = @(,@("192.212.1.30", 1433)) If you want it to look better, I’d recommend an array of hashtables which would make the code a little more readable. $tbl = @{ip="192.212.1.30"; port=1443}

$array = @($tbl)

Here is a bit cleaner approach:

$params = @{ ComputerName = "192.212.1.30" Port = 1433 } Test-NetConnection @params | Select-Object -Property @{Name='DateTested';Expression={Get-Date -format "dd-MMM-yyyy HH:mm:ss"}}, @{Name='SourceIP';Expression={(Test-Connection -ComputerName$_.ComputerName -Count 1).IPV4Address.IPAddressToString}},
ComputerName,
RemotePort,
TcpTestSucceeded

Thanks for the reply, can you show how these would work with multiple entries please?

If your excel files are in .csv format and have an ‘IP’ and ‘Port’ column headers, this will work. No need to get ipaddress from test-connection, you have that in your excel files.

# Find csv files, get info, then export objects
(Get-ChildItem -Path \\path\to\excel_csvfiles -Filter 'excel*.csv').FullName |
Import-Csv | ForEach-Object {
$time = get-date -Format 'dd-MMM-yyyy HH:mm:ss'$test = Test-NetConnection -ComputerName $_.IP -Port$_.Port
[PSCustomObject]@{
"Date Tested"    = $time "Source IP" =$_.IP
ComputerName     = $test.computername RemotePort =$test.remoteport
RemoteAddress    = $test.remoteaddress TcpTestSucceeded =$test.tcptestsucceeded
}
} | Export-Csv -Path \\path\to\excel_csvfiles\result.csv -NoTypeInformation -Append


In this particular instance the way I’m having to craft this is a little odd. Instead of being able to read a file directly from this script, my source is a rather bigger spreadsheet. Consequently there is lots of logic that has to be done to extract the data that I need, the result being that the PS script is crafted as a string (in C#) and then outputted.

Therefore, the ability to define each item like this is important:

$array1 = “192.212.1.30”, 1433 $array2 = “192.213.1.30”, 1433