Finding port no for Analysis servers for all the servers across the estate

Hi Mates,

Today I am going to share simple script to find the port number for analysis servers across the estate. Recently I was involved in Migration of Analysis server databases from SQL Server 2008 R2 to Higher Versions.

As part of that we need to send a request to firewall team to open the ports between Database Engine and Analysis servers.


We got around 25 servers so if I follow manual approach means either reading 
netstat -abno>out.txt and then looking for the process (msmdsrv.exe.) or else reading the msmdredir.ini file.

Both  of them would be time consuming hence thought of checking with power-shell for the same. 

In our environment we were using named instances hence just like Database Engine dynamic ports would get assigned to them automatically.



$servers='ABC','MNO','XYZ'
foreach($servr in $servers)
{
get-nettcpconnection -CimSession $servr | where {$_.State -eq 'Listen' -and  $_.LocalAddress -ne '::'} | where {$_.OwningProcess -in (Get-Process -ComputerName $servr | Where {$_.Name -eq 'msmdsrv'}).Id} |
Select-Object -Property  LocalPort,pscomputername
}

Note: I didn't make use of try catch if the server is not reachable as I am just keen on how to retrieve the port as I didn't find any script on google.

Will come up with another post till that time happy reading.

Comments

Thanks a lot for sharing information..nice and information sir.