How to check port connectivity for list of windows servers

Question

As a database admin in a microsoft shop, my team supported several database servers, with hundreds of databases, used by lots of servers. It's no secret that firewall rules can be applied in non-prod without making it to production or prod standby (dr). You can have it working perfectly in prod, then failover and have connections fail because ports aren't open for the secondary servers.

this will output whether connectivity was possible between from the source through the port to the destination server — ClaireCotter, Feb 23 '23 at 00:39
If this is intended to be a self answered question, then please format it as such, with a clear question and then add an actual answer. — Dale K, Feb 23 '23 at 00:52
@DaleK - im not following. what is unclear about this title? How to check port connectivity for list of windows servers. are you saying the answer should be moved to a response ? — ClaireCotter, Mar 01 '23 at 15:52

score 0 · Answer 1 · answered Mar 01 '23 at 15:56

I wrote this script to remote into windows servers hosting our integrations and test port connectivity.

create a .ps1 script with this content

#--for windows hosts-- output to array then dump array to file
#https://stackoverflow.com/questions/41130310/change-directory-in-powershell
Set-Location -Path  C:\Users\foo_user_name\Desktop

#https://social.technet.microsoft.com/Forums/windows/en-US/78d5a5fa-bb82-4c2d-a2c1-96d518b9bd74/need-to-read-text-file-as-an-array-and-get-elements-from-each-line-of-array-using-powershell?forum=winserverpowershell
$DB = Get-Content SourcePortDestinationTestCases.csv | select-object -skip 1 #skip the first header row

#https://ss64.com/ps/syntax-arrays.html
$Resultlist = new-object system.collections.arraylist


#https://social.technet.microsoft.com/Forums/en-US/4452976f-47fd-430d-b65f-8fd3f33fff0e/get-date-format-powershell?forum=winserverpowershell
$date = get-date
$date =$date.Tostring("yyyyMMdd") 
$newfilename = "SourcePortDestinationTestCasesResults" + $date+ ".csv"

#https://blogs.technet.microsoft.com/heyscriptingguy/2015/06/07/powertip-use-powershell-to-create-new-file/
New-Item   $newfilename -ItemType file -Force


$OldServer =''
foreach ($Data in $DB) {
  $Source, $Port, $Destination = $Data -split ',' -replace '^\s*|\s*$'
  
  write-host "SourceServer is: "$Source
  write-host "Port is: "$Port
  write-host "Destination is: "$Destination
  If (-NOT ($Source -eq  $OldServer)) {
     Exit-PSSession -
     Enter-PSSession -ComputerName $Source 
  }
  #https://social.technet.microsoft.com/Forums/ie/en-US/7a3304c7-b564-4acc-ab28-2648a20f4bce/telnet-using-powershell?forum=winserverpowershell
   If ( Test-Connection $Destination -Count 1 -Quiet) {
    
        try {       
            $null = New-Object System.Net.Sockets.TCPClient -ArgumentList $Destination,$Port
            $props = @{
                SourceServer = $Source
                Port = $Source
                DestinationServer = $Destination
                PortOpen = 'Yes'
            }# | Format-Table | Out-File -FilePath $newfilename -Append 
            $PortOpen = 'Yes'  
            
        }

        catch {
            $props = @{
                SourceServer = $Source
                Port = $Port
                DestinationServer = $Destination
                PortOpen = 'No'
            } #| Format-Table | Out-File -FilePath  $newfilename -Append 
            $PortOpen = 'No'
        }
    }

    Else {
        
        $props = @{
            SourceServer = $Source
            Port = $Port
            DestinationServer = $Destination
            PortOpen = 'Server did not respond to ping'
        } #| Format-Table | Out-File -FilePath  $newfilename -Append 
        $PortOpen = 'Server did not respond to ping'
    }

    #https://community.spiceworks.com/topic/1358008-building-an-array-list-and-exporting-to-a-csv
    $Output = New-Object -Type PSCustomObject 
    $output | Add-Member -MemberType NoteProperty -Name "Source" -value $Source
    $output | Add-Member -MemberType NoteProperty -Name "Port" -value $Port
    $output | Add-Member -MemberType NoteProperty -Name "Destination" -value $Destination
    $output | Add-Member -MemberType NoteProperty -Name "PortOpen" -value $PortOpen

    $Resultlist.add($Output) | Out-Null
    New-Object PsObject -Property $props
    $OldServer = $Source
  
}

$Resultlist | Export-Csv $newfilename 
$Resultlist[0][0][0][0]
exit

create a csv called SourcePortDestinationTestCases.csv fill with your source, port, and destinations you have to be able to remote into the windows server for this to work. here's the csv format

Source,Port,Destination
ServerA,6000,ServerB

How to check port connectivity for list of windows servers

1 Answers1