List the Users and Client Endpoint Versions Connected to a Registrar Pool: Remote Connection

  • Article

Nick Smith and Scott Stubberfield have updated a previous script of theirs which lists all the users connected to a Registrar pool along with the client version of the endpoint they used to log on to the system. According to Nick, updates made since the time the previous script was published include the following:

  • The script outputs the results to the screen instead of a .CSV file. This allows administrators to pipe the results to cmdlets like Where-Object.
  • The script prompts for the pool FQDN and automatically queries all servers that are members of the pool.
  • There are two versions of the script.
    • The Get-PoolUserRegistrations.ps1 script connects directly to the SQL instance on the remote server. This requires that the Windows firewall be disabled or configured to allow SQL connections.
    • The Get-PoolUserRegistrations_Remoting.ps1 script (this one) uses Windows PowerShell remoting to run the SQL query locally on each machine. With this script, the Windows firewall does not need to be disabled; however, you must run the Enable-PSRemoting cmdlet on each server in order to allow remote connections.

The net result is that anyone running the script will get back data similar to this:

ClientVersion UserName Fqdn

------------- -------- ----

UCCAPI/4.0.7629.0... kenmyer@litwareinc.com atl-cs-001.litwareinc.com

UCCAPI/4.0.7629.0... packerman@litwareinc.com atl-cs-001.litwareinc.com

UCCAPI/4.0.7629.0... pdoyle@litwareinc.com atl-cs-001.litwareinc.com

To make use of this script, copy the code shown below, paste it into a text editor (such as Notepad) and then save the script as a .PS1 file (for example, C:\Scripts\Get-PoolUserRegistrations.ps1). From the Lync Server Management Shell you can then run the script by referencing the script file path followed by the fully qualified domain name of the Registrar pool you want to return information from. For example:

C:\Scripts\Get-PoolUserRegistrations.ps1 –PoolFqdn atl-cs-001.litwareinc.com

If you do not include a Registrar pool the script will prompt you to enter a pool FQDN.

Here's the code:

Param (
    $PoolFQDN = (Read-Host -Prompt "Please enter the pool fqdn")
)

#Get all the CS pool information
$CSPool = Get-CSPool $PoolFQDN

#Create empty variable that will contain the user registration records
$overallrecords = $null

#Create the scirptblock to execute on remote server
$RemoteScriptBlock = {
    #Defined Connection String
    $connstring = "server=.\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, `
        RE.EndpointId as EndpointId, `
        EP.ExpiresAt, `
        '$computer' as RegistrarFQDN `
        From `
       rtcdyn.dbo.RegistrarEndpoint RE `
       Inner Join `
        rtc.dbo.Resource R on R.ResourceId = RE.OwnerId `
       Inner Join `
        rtcdyn.dbo.Endpoint EP on EP.EndpointId = RE.EndpointId `
        Order By ClientVersion, UserName "
    
    #Make the connection to Server    
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connstring
    $connection.Open()
    $command.Connection = $connection
    
    #Get the results
    $sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $sqladapter.SelectCommand = $command
    $results = New-Object System.Data.Dataset    
    $recordcount=$sqladapter.Fill($results)
    
    #Close the connection
    $connection.Close()

    $Results.Tables[0]
}

#Loop through a front end computers in the pool
Foreach ($Computer in $CSPool.Computers){

    #Get computer name from fqdn
    $ComputerName = $Computer.Split(".")[0]

    $PSSession = New-PSSession -Computername $ComputerName
   
    $overallrecords = $overallrecords + (Invoke-Command -Session $PSSession -ScriptBlock $RemoteScriptBlock)
        
    Remove-PSSession $PSSession
}

$overallrecords