Retrieve Lync Connection Information

[NextHop_Team]

First published on TECHNET on Mar 22, 2011

Submitted by Tracy A. Cerise, University of Kentucky

This script pulls Microsoft Lync 2010 connection information, such as the number of connections per Front End Server and number of unique users, and either displays it to the console or writes it to a file, depending on the parameters you supply with the call.

To use this script, copy the code shown below, paste it into a text editor (like Notepad) and then save the file with a .PS1 extension (for example, C:ScriptsConnections.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:ScriptsConnections.ps1

For a full description as well as requirements for connecting to the Front End Server database, see the introductory comments in the script below. For additional help on using this script, type the following at the Lync Server Management Shell prompt and press ENTER:

C:ScriptsConnections.ps1 -help

#################################################################################################

# Connections.ps1

#

# Program to pull Lync connection information.

# This program will pull complete information across all

# frontend servers in a pool. It can also be used to find

# specific connection information on an individual user by

# supplying the user's sip address. The parameter for

# the pool to access for connection information can be

# pre-populated so it doesn't have to be passed on the command line.

#

# ACKNOWLEDGEMENT: This program's database connection information

# was originally taken from the "List Connections

# to Registrar Pools" submitted by Scott Stubberfield

# and Nick Smith from Microsoft to the Lync 2010

# PowerShell blog

# (http://blogs.technet.com/b/csps/) on June 10, 2010.

#

# NOTE: In order to gain remote access to each frontend server's

# RTCLOCAL database where connection information is found,

# you need to open the local firewall for port 1434.

# Also, need to go into the SQL Server Configuration Manager

# and for RTCLOCAL, enable named pipes and restart the SQL

# service for the named pipes to take effect.

#

# Port 1434 is required in order to make the connection to

# the named instance RTCLOCAL on the remote machines.

#

#

# Written by: Tracy A. Cerise (tracy@uky.edu)

# Date: March 2011

#

# Modification History

# --------------------

#

# Could add # users with enterprise voice enabled and other

# individual stats to this page as well

#

#################################################################################################

##########################################################

# Commandline Parameters to use for running this program #

##########################################################

param($Pool = "your.pool.here", $SIPAddr, $FilePath, [switch] $Help)

#################################################################################################

################################# Functions #########################################

#################################################################################################

function GetData {

param ($sipAddr = $null, $server)

##############################################################################################

# Went to using a named parameter for this function due to the

# way Powershell does its thing with parameter passing, which

# is NOT GOOD! At any rate, need to call this function

# as you would from a command line: GetData -sipAddr "value"

# -server "value"

#

# Also, assuming a value of NULL for the SIP address of an

# individual user, mostly to use this for finding overall

# values, only occasionally to seek specific users.

##############################################################################################

if ($sipAddr) {

$whereClause = "where R.UserAtHost = '$sipAddr' "

}

else {

$whereClause = $null

}

#Define SQL Connection String

$connstring = "server=$serverrtclocal;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 `

$whereClause `

Order By ClientVersion, UserName "

$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()

return $Results.Tables[0]

}

#################################################################################################

function Help {

"

NAME

Connections.ps1

SYNOPSIS

Returns current connection count for all frontend servers in a given pool

including a breakdown of connection by client, frontend server and users.

It can also be used to return connection information on an individual user.

SYNTAX

Connections.ps1 [-Pool <PoolFQDN>] [-SIPAddr] [-FilePath] [-help]

DESCRIPTION

This program will return a connection count for a given pool. The program

can be edited to set a default pool. You will also be able to get

information on an individual user by providing the users SamAccountName.

EXAMPLES

-------------------------- EXAMPLE 1 --------------------------

C:PS>connections.ps1

Description

-----------

Returns information on all connections on all frontend servers in the

default pool that has been hard-coded into the program.

-------------------------- EXAMPLE 2 --------------------------

C:PS>connections.ps1 -Pool alternate.pool.fqdn