Get-SQLServers.ps1

function Get-SQLServers
{
    
    function Connect-VMWare{
    
        #Requires -Modules 'VMware.PowerCLI'

        $Server = 'vcenter.domain.local'

        $VMCli = (Get-Module VMware.PowerCLI -ListAvailable).Name
        if($VMCli -ne 'VMware.PowerCLI'){
    
            Write-Output "No VMWare CLI Found"
        
            Install-Module VMware.PowerCLI -Scope CurrentUser #-AllowClobber #-Force
		
        }
    
        Import-Module VMware.PowerCLI 
		
        Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP $false -InvalidCertificateAction Ignore -Confirm:$False

        $UPN = cmd /c whoami /upn
        $Cred = (Get-Credential -UserName $UPN -Message "Password")
        Connect-VIServer -Credential $Cred -Server $Server   # Requires the Credential function to be run once
    
    }
    Connect-VMWare

    $VMs   = Get-VM | Select Name,PowerState | Sort Name  
    $VMsOn = ($VMs | Where {($_.PowerState -eq 'PoweredOn')}).Name

    # Initialise Array
    $SQLSuccess = @()
    $SQLFail = @()

    # SET TOP ROW
    $SQLSuccess += '"Device"' + '","' + '"RemoteAddress"' '","' + '"RemotePort"' + '","' + '"TcpTestSucceeded"'
    $SQLFail    += '"Device"' + '","' + '"RemoteAddress"' + '","' + '"RemotePort"' + '","' + '"TcpTestSucceeded"'
    
    foreach($VM in $VMsOn){
        
        $VM

        $Port1 = 1433
        $Port2 = 1587
        
        #Test-NetConnection -ComputerName SQL3  -Port $Port2 # TESTING
        $P1Test = Test-NetConnection -ComputerName $VM -Port $Port1 
        if($P1Test.TcpTestSucceeded -eq $true){
            
           Write-Host -f Green "Test SUCCESSFUL of $($VM) on $($Port1)`r`n"    
           $SQLSuccess += '"' + $P1Test.ComputerName +'","'+ $P1Test.RemoteAddress + '","' + $P1Test.RemotePort + '","' + $P1Test.TcpTestSucceeded +'"'
     
        }

        #Test-NetConnection -ComputerName SQL3  -Port $Port2 # TESTING
        $P2Test = Test-NetConnection -ComputerName $VM -Port $Port2
        if($P2Test.TcpTestSucceeded -eq $true){
            
           Write-Host -f Green "Test SUCCESSFUL of $($VM) on $($Port2)`r`n"    
           $SQLSuccess += '"' + $P2Test.ComputerName +'","'+ $P2Test.RemoteAddress + '","' + $P2Test.RemotePort + '","' + $P2Test.TcpTestSucceeded +'"'
     
        }
        
        #FAILURES
        if($P1Test.TcpTestSucceeded -eq $false){
            
            Write-Warning "Test FAILED of $($VM) on $($Port1)`r`n"
            $SQLFail += '"' + $P1Test.ComputerName +'","'+ $P1Test.RemoteAddress + '","' + $P1Test.RemotePort + '","' + $P1Test.TcpTestSucceeded +'"'
        }
        if($P2Test.TcpTestSucceeded -eq $false){
            
            Write-Warning "Test FAILED of $($VM) on $($Port2)`r`n"
            $SQLFail += '"' + $P2Test.ComputerName +'","'+ $P2Test.RemoteAddress + '","' + $P2Test.RemotePort + '","' + $P2Test.TcpTestSucceeded +'"'
        }
    
    }#END FOREACH BIG
    
    $SQLSuccess | Out-File 'C:\Temp\Server_SQLPort_Success.csv' 
    $SQLFail | Out-File 'C:\Temp\Server_SQLPort_Failure.csv' 

}