List Connections to Registrar Pools

  • Article

Submitted by Scott Stubberfield and Nick Smith, Microsoft

So just who is connected to your Registrar pool, and how many people are connected to Pool A vs. Pool B? Don’t ask us; we have no idea whatsoever. Instead, you should ask Scott Stubberfield and Nick Smith, who’ve written a script that can contact your front-end pools and return this distribution information. Their script connects to the backend Microsoft Lync Server 2010 databases and retrieves information about the endpoints currently connected to two different Registrar pools. The retrieved information, sorted by client version and by user name, is then saved to a comma-separated values file named PoolDistribution.csv.

Here’s the code:

#Defined Connection String

$connstring = "server=p10-CSFE01\rtclocal;database=rtcdyn;`
trusted_connection=true;"

$connstring2 = "server=p10-CSFE02\rtclocal;database=rtcdyn; `
trusted_connection=true;"

#Define SQL Command

$command = New-Object System.Data.SqlClient.SqlCommand

$command.CommandText = "Select (cast (RE.ClientApp as `
varchar (100))) as ClientVersion, `

    R.UserAtHost as UserName, `

    Reg.Fqdn `

    From `

    rtcdyn.dbo.RegistrarEndpoint RE `

    Inner Join `

    rtc.dbo.Resource R on R.ResourceId = RE.OwnerId `

    Inner Join `

    rtcdyn.dbo.Registrar Reg on Reg.RegistrarId = `
RE.PrimaryRegistrarClusterId `

    Order By ClientVersion, UserName "

#Make the connection to Server 1

$connection = New-Object System.Data.SqlClient.SqlConnection

$connection.ConnectionString = $connstring

$connection.Open()

$command.Connection = $connection

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter

$sqladapter.SelectCommand = $command

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#Make the connection to Server 2

$connection.ConnectionString = $connstring2

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 2

$overallrecords | Export-Csv "PoolDistribution.csv"

Write-Host -ForegroundColor Green "Query complete"

To use this script, copy the code shown above, paste it into a text editor (like Notepad) and then save the file with a .PS1 extension (for example, C:\Scripts\PoolDistribution.ps1). After that, you can run the script from within the Lync Server Management Shell simply by typing the full path to the .PS1 file and then pressing ENTER:

C:\Scripts\PoolDistribution.ps1

As written, the script is designed to connect to two different Registrar pools: p10-CSFE01 and p10-CSFE02. If you only want to connect to one Registrar pool, remove one of the pool names listed at the beginning of the script, and remove (or comment out) the following block of code:

#Make the connection to Server 2

$connection.ConnectionString = $connstring2

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 2

To connect to additional Registrar pools you must do two things. First, add the new pools to the set of pools listed at the beginning of the script. For example, if you need to add the pool p10-CSFE03 the first part of your script should look similar to this:

#Defined Connection String

$connstring = "server=p10-CSFE01\rtclocal;database=rtcdyn;`
trusted_connection=true;"

$connstring2 = "server=p10-CSFE02\rtclocal;database=rtcdyn; `
trusted_connection=true;"

$connstring3 = "server=p10-CSFE03\rtclocal;database=rtcdyn; `
trusted_connection=true;"

Note the variable name: $connstring3.

Next, copy the following block of code, changing the comments so they refer to Server/Section 3, and using the variable $connstring3 instead of $connstring2. In other words:

#Make the connection to Server 3

$connection.ConnectionString = $connstring3

$connection.Open()

$command.Connection = $connection

$results = New-Object System.Data.Dataset

$recordcount=$sqladapter.Fill($results)

$connection.Close()

$overallrecords = $overallrecords + $Results.Tables[0]

#End Section 3

To add additional pools just repeat those steps as needed.