Get IP Address and Port Details of Database Servers and Instances

If you want to find out the IP Addresses and Port number information for all your SQL servers and the instances configured in the SQL cluster, the below script will be helpful. It will require an input file Get-IPDetail_Input.txt where you have to specify the servers and instances you want the details for. The input file will look as below:

Input file:

Server1
Server2
Server2\Instance1
Server2\Instance2
Server3\Instance1

Script:

###################################################################################################################
# Title : Get IP Address and Port Details
# Created By : Kaustubh Doshi <kikaustubh.doshi@hotmail.com>
# Description : Fethes IP Address, Port Number, Windows Server Name and MS-SQL Instance Name for the
# from "Get-IPDetail_Input.txt" input file. These all details are collected in '.csv' file named 
# "Get-IPDetail_Output.csv". Script further can be manipulated by adding fewer details as expected.
# Versions
# v1.0 : Get-IPDetails script prepated to fetching important details.
###################################################################################################################

Import-Module sqlps

$Query_Machine = "SELECT SERVERPROPERTY('MACHINENAME')"
$Query_SQLInstance = "SELECT SERVERPROPERTY('SERVERNAME')"
$Query_IP = "SELECT local_net_address, local_tcp_port FROM sys.dm_exec_connections WHERE SESSION_ID = @@SPID"

$sql = Get-Content -Path 'D:\Get-IPDetail_Input.txt'
$AllMachinIPs = @()

Foreach ($ser in $sql)
{
 $HostName = invoke-sqlcmd -ServerInstance $ser -database master -query $Query_Machine
 $MSSQLInstanceName = invoke-sqlcmd -ServerInstance $ser -database master -query $Query_SQLInstance
 $IPDetails = invoke-sqlcmd -ServerInstance $ser -database master -query $Query_IP
 
 $Properties = @{
 'Windows_Node' = $($HostName.Column1)
 'MS-SQL_Instance' = $($MSSQLInstanceName.Column1)
 'IP_Address' = $($IPDetails.local_net_address)
 'Port_Number' = $($IPDetails.local_tcp_port)
 }
 
 $AllMachinIPs += New-Object psobject -Property $Properties
 Write-Host " $($HostName.Column1) - $($MSSQLInstanceName.Column1) - $($IPDetails.local_net_address) - $($IPDetails.local_tcp_port)"
}

$AllMachinIPs | Select-Object 'Windows_Node', 'MS-SQL_Instance', 'IP_Address', 'Port_Number' | Export-Csv -Path 'D:\Get-IPDetail_Output.csv'

 1,911 total views,  2 views today