Automation to list all TCP static and dynamic ports of SQL Server instances using PowerShell

I was asked by the reader on how to get all the port information of SQL Servers instances. As you all know that PowerShell is built on .Net automation framework. I would prefer PowerShell to design any such requirements than any other languages.

In this post, I’ll show to how to gather the information without logging on to each server.

Prerequisites

  • PowerShell 3.0 or above
  • WMI Service is enabled on all the target machine

The Server List is fed to the PowerShell script as an Input and output lists the ServerName, InstanceName, TCPPort and it’s the corresponding value

Method 1: Iterating over the Input file

The input file Server.csv contains the list of servers

For example,

Now, run the following PoSH script. In the script, you need to change the Inputfilename parameter.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values
$Results=@()
#the servers are listed. Make WMI service is enabled on all the target machine
#Import the server list - Read the servers from the c:\server123.csv file.
Import-Csv $Inputfilename |% {
$namespace = Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1
$port=Get-WmiObject -ComputerName $_.server -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName | where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''} foreach ($p in $port)
{
$Properties = @{
Servername = $_.Server
Instancename = $p.instanceName
PropertyName = $p.PropertyName
port=$p.propertystrval
}
$Results += New-Object psobject -Property $properties
}
}
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

Output

Method 2: Direct feed of Servers as an array

In some scenarios where you don’t prefer to take input from a file. In this case, you can directly feed the server names as an array to the script.

#input file name
$Inputfilename='c:\server123.csv'
#An array to hold the output values
$Results=@()
'hqdbt01','hqdbsp18','hqdbsp17'|%{
$namespace = Get-WmiObject -ComputerName $_ -Namespace "root\microsoft\sqlserver" -Class "_Namespace" -Filter "name like 'ComputerManagement%'" | sort desc | select -ExpandProperty name -First 1
$port=Get-WmiObject -ComputerName $ -Namespace "root\microsoft\SqlServer\$namespace" -Class ServerNetworkProtocolProperty | select instancename,propertystrval,PropertyName,IPAddressName,ProtocolName | where{$_.IPAddressName -eq 'IPAll' -and $_.propertystrval -ne ''}
foreach ($p in $port)
{
$Properties = @{
Servername = $_
Instancename = $p.instanceName
PropertyName = $p.PropertyName
Port=$p.propertystrval
}
$Results += New-Object psobject -Property $properties
}
}
$Results |select ServerName,InstanceName,PropertyName,Port |Format-Table -AutoSize

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s