Test SQL Connection

Function Test-SqlConnection
{
<# 
.SYNOPSIS 
 Tests a the connection to a single SQL instance.

.EXAMPLE
Test-SqlConnection DBServer01

#>    
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [Alias("ServerInstance", "SqlInstance")]
        [object]$SqlServer,
        [System.Management.Automation.PSCredential]$SqlCredential
    )
    
    
    # Get local enviornment
    Write-Output "Getting local enivornment information"
    $localinfo = @{ } | Select-Object Windows, PowerShell, CLR, SMO, DomainUser, RunAsAdmin
    $localinfo.Windows = [environment]::OSVersion.Version.ToString()
    $localinfo.PowerShell = $PSVersionTable.PSversion.ToString()
    $localinfo.CLR = $PSVersionTable.CLRVersion.ToString()
    $smo = (([AppDomain]::CurrentDomain.GetAssemblies() | Where-Object { $_.Fullname -like "Microsoft.SqlServer.SMO,*" }).FullName -Split ", ")[1]
    $localinfo.SMO = $smo.TrimStart("Version=")
    $localinfo.DomainUser = $env:computername -ne $env:USERDOMAIN
    $localinfo.RunAsAdmin = ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")
    
    # SQL Server
    if ($SqlServer.GetType() -eq [Microsoft.SqlServer.Management.Smo.Server]) { $SqlServer = $SqlServer.Name.ToString() }
    
    $serverinfo = @{ } | Select-Object ServerName, BaseName, InstanceName, AuthType, ConnectingAsUser, ConnectSuccess, SqlServerVersion, AddlConnectInfo, RemoteServer, IPAddress, NetBIOSname, RemotingAccessible, Pingable, DefaultSQLPortOpen, RemotingPortOpen
    
    $serverinfo.ServerName = $sqlserver
    
    Write-Output "Determining SQL Server base address"
    $baseaddress = $sqlserver.Split("\")[0]
    try { $instance = $sqlserver.Split("\")[1] }
    catch { $instance = "(Default)" }
    if ($instance -eq $null) { $instance = "(Default)" }
    
    if ($baseaddress -eq "." -or $baseaddress -eq $env:COMPUTERNAME)
    {
        $ipaddr = "."
        $hostname = $env:COMPUTERNAME
        $baseaddress = $env:COMPUTERNAME
    }
    
    $serverinfo.BaseName = $baseaddress
    $remote = $baseaddress -ne $env:COMPUTERNAME
    $serverinfo.InstanceName = $instance
    $serverinfo.RemoteServer = $remote
    
    Write-Output "Resolving IP address"
    try
    {
        $hostentry = [System.Net.Dns]::GetHostEntry($baseaddress)
        $ipaddr = ($hostentry.AddressList | Where-Object { $_ -notlike '169.*' } | Select-Object -First 1).IPAddressToString
    }
    catch { $ipaddr = "Unable to resolve" }
    
    $serverinfo.IPAddress = $ipaddr
    
    Write-Output "Resolving NetBIOS name"
    try
    {
        $hostname = (Get-WmiObject -Class Win32_NetworkAdapterConfiguration -Filter IPEnabled=TRUE -ComputerName $ipaddr -ErrorAction SilentlyContinue).PSComputerName
        if ($hostname -eq $null) { $hostname = (nbtstat -A $ipaddr | Where-Object { $_ -match '\<00\> UNIQUE' } | ForEach-Object { $_.SubString(4, 14) }).Trim() }
    }
    catch { $hostname = "Unknown" }
    
    $serverinfo.NetBIOSname = $hostname
    
    
    if ($remote -eq $true)
    {
        # Test for WinRM #Test-WinRM neh
        Write-Output "Checking remote acccess"
        winrm id -r:$hostname 2>$null | Out-Null
        if ($LastExitCode -eq 0) { $remoting = $true }
        else { $remoting = $false }
        
        $serverinfo.RemotingAccessible = $remoting
        
        Write-Output "Testing raw socket connection to PowerShell remoting port"
        $tcp = New-Object System.Net.Sockets.TcpClient
        try
        {
            $tcp.Connect($baseaddress, 135)
            $tcp.Close()
            $tcp.Dispose()
            $remotingport = $true
        }
        catch { $remotingport = $false }
        
        $serverinfo.RemotingPortOpen = $remotingport
    }
    
    # Test Connection first using Test-Connection which requires ICMP access then failback to tcp if pings are blocked
    Write-Output "Testing ping to $baseaddress"
    $testconnect = Test-Connection -ComputerName $baseaddress -Count 1 -Quiet
    
    $serverinfo.Pingable = $testconnect
    
    # SQL Server connection
    
    if ($instance -eq "(Default)")
    {
        Write-Output "Testing raw socket connection to default SQL port"
        $tcp = New-Object System.Net.Sockets.TcpClient
        try
        {
            $tcp.Connect($baseaddress, 1433)
            $tcp.Close()
            $tcp.Dispose()
            $sqlport = $true
        }
        catch { $sqlport = $false }
        $serverinfo.DefaultSQLPortOpen = $sqlport
    }
    else { $serverinfo.DefaultSQLPortOpen = "N/A" }
    
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
    
    try
    {
        if ($SqlCredential -ne $null)
        {
            $username = ($SqlCredential.username).TrimStart("\")

            if ($username -like "*\*")
            {
                $username = $username.Split("\")[1]
                $authtype = "Windows Authentication with Credential"
                $server.ConnectionContext.LoginSecure = $true
                $server.ConnectionContext.ConnectAsUser = $true
                $server.ConnectionContext.ConnectAsUserName = $username
                $server.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password
            }
            else
            {
                $authtype = "SQL Authentication"
                $server.ConnectionContext.LoginSecure = $false
                $server.ConnectionContext.set_Login($username)
                $server.ConnectionContext.set_SecurePassword($SqlCredential.Password)
            }
        }
        else
        {
            $authtype = "Windows Authentication (Trusted)"
            $username = "$env:USERDOMAIN\$env:username"
        }
    }
    catch
    {
        Write-Exception $_
        $authtype = "Windows Authentication (Trusted)"
        $username = "$env:USERDOMAIN\$env:username"
    }
    
    $serverinfo.ConnectingAsUser = $username
    $serverinfo.AuthType = $authtype
    
    
    Write-Output "Attempting to connect to $SqlServer as $username "
    try
    {
        $server.ConnectionContext.ConnectTimeout = 10
        $server.ConnectionContext.Connect()
        $connectSuccess = $true
        $version = $server.Version.ToString()
        $addlinfo = "N/A"
        $server.ConnectionContext.Disconnect()
    }
    catch
    {
        $connectSuccess = $false
        $version = "N/A"
        $addlinfo = $_.Exception
    }
    
    $serverinfo.ConnectSuccess = $connectSuccess
    $serverinfo.SqlServerVersion = $version
    $serverinfo.AddlConnectInfo = $addlinfo
    
    Write-Output "`nLocal PowerShell Enviornment"
    $localinfo | Select-Object Windows, PowerShell, CLR, SMO, DomainUser, RunAsAdmin
    
    Write-Output "SQL Server Connection Information`n"
    $serverinfo | Select-Object ServerName, BaseName, InstanceName, AuthType, ConnectingAsUser, ConnectSuccess, SqlServerVersion, AddlConnectInfo, RemoteServer, IPAddress, NetBIOSname, RemotingAccessible, Pingable, DefaultSQLPortOpen, RemotingPortOpen
    
}

Simple Python Subnet Calculator

#!/usr/bin/env python3
# Use: ./subnet.py <ip/cidr>
# Alt: ./subnet.py <ip> <mask>

import sys

if __name__=="__main__":
    addr = [0, 0, 0, 0]
    mask = [0, 0, 0, 0]
    cidr = 0
    
    if len(sys.argv) == 2:
        (addr, cidr) = sys.argv[1].split('/')
        
        addr = [int(x) for x in addr.split(".")]
        cidr = int(cidr)
        mask = [( ((1<<32)-1) << (32-cidr) >> i ) & 255 for i in reversed(range(0, 32, 8))]
    elif len(sys.argv) == 3:
        addr = sys.argv[1]
        mask = sys.argv[2]
        
        addr = [int(x) for x in addr.split(".")]
        mask = [int(x) for x in mask.split(".")]
        cidr = sum((bin(x).count('1') for x in mask))
    else:
        print("Use: {0} <ip/cidr>".format(sys.argv[0]))
        print("Alt: {0} <ip> <mask>".format(sys.argv[0]))
        sys.exit(-1)
        
    netw = [addr[i] & mask[i] for i in range(4)]
    bcas = [(addr[i] & mask[i]) | (255^mask[i]) for i in range(4)]
    
    print("Address: {0}".format('.'.join(map(str, addr))))
    print("Mask: {0}".format('.'.join(map(str, mask))))
    print("Cidr: {0}".format(cidr))
    print("Network: {0}".format('.'.join(map(str, netw))))
    print("Broadcast: {0}".format('.'.join(map(str, bcas))))