Script: Get-CsConnections.ps1 – See User Connections, Client Versions, Load Balancing in Lync Server
At this point, I don’t have a full list of Windows Server firewall exceptions for the script to run on Windows Server 2012/Lync Server 2013. I have verified it works fine if the Domain Profile is turned off. If you determine what exact exceptions are needed, please forward them. Thanks!
Tracy A. Cerise and Mahmoud Badran came up with a script to show Lync connections, and the users connected. This was quite informative as it could be used to show load balance distribution, client versions being used, and more.
I took the script and updated it a little, including:
- Removed the help function and the header block and inserted comment based help. So a user can run get-help Get-CsConnections.ps1 and get the help, just like any other script and cmdlet.
- Added a parameter to display the user list. My needs didn’t require the user list – just the statistics at the beginning. So I added the feature to show the user list by running Get-CsConnections.ps1 –IncludeUsers.
- Added a couple of functions, including one that cleans up some variables when exiting.
- Adjusted some of the formatting. I noticed things didn’t always line up when the server FQDNs were really long, like those in child domains.
- Did a prereq check to verify the Lync module is loaded. If not, it gets loaded. That way, the script will still run fine if it’s run from an ordinary PowerShell console.
- Accounted for the pool parameter being just a NetBIOS name by adding the $env:userdnsdomain to the NetBIOS name to create the FQDN. This appears to work fine if the Lync servers and user running the script are both in the same domain. If not, then an FQDN would be required.
- Renamed the script to Get-CsConnections.ps1 and some of the functions to the normal verb-noun format.
- Added a feature to show just a specific client version number, and the users connected with that client version. This can help you determine who is connecting with what versions, which is helpful when looking into licensing, upgrades, etc.
- Added support for Lync Server 2013.
- Tons more info in updates and releases following that. See the changelog for more info.
Syntax
Get-CsConnections.ps1 [[-Pool] ] [-Is2013] [[-SIPAddress] ] [[-FilePath] ] [-IncludeUsers] [-IncludeHighUsers] [-IncludeSystem] [[-UserHighConnectionFlag] ] [[-ClientVersion] ] [-ShowFullClient] [ShowTotal] [[-Server] ] [-WhatIf] [-Confirm] []
Run the script specifying the front end pool or server to target:
Get-CsConnections.ps1 -pool [pool FQDN]
or
Get-CsConnections.ps1 -server [server FQDN]
If you’re running it against a 2013 pool or server, you MUST specify the -Is2013 switch, or the script won’t return any results.
Get-CsConnections.ps1 -pool [pool FQDN] -Is2013
If I can find an auto-detect method for server versions, I’ll include that in a later build.
Will show you unique client versions, their user agent, and the number of connections for each:
Current connected users listed by client name/version
Distribution of connections across frontend servers (load balancing):
Connections by server (load balancing)
The number of unique users and clients connected:
Total unique users and clients
And, adding the -IncludeUsers switch, such as:
Get-CsConnections.ps1 -pool [pool FQDN] -IncludeUsers
will also show the users who are connected, and the number of connections they each have:
Connections per user
Using -IncludeHighUsers instead of -IncludeUsers will only list those users who meet the UserHighConnectionFlag (shown in white) or exceed the UserHighConnectionFlag (shown in red).
Get-CsConnections.ps1 -SipAddr [sip address] -pool [pool FQDN]
Will show you the information for a single user:
Connection info for a specific user
Get-CsConnections.ps1 -pool [pool FQDN] -ClientVersion [version number]
Will show the connection data for just that version number, including listing the users connected with that client version. This is helpful if the first method lists some version numbers you’d like to track down. Here, I used a client version of 13.1. MC/13.1.x is the OCS client on the Mac.
Connections by client version
Using the -ShowFullClient option will show extended info for client name/version. However, the previous ‘Client Version’ column is not shown due to formatting restrictions. Here we can see more info, especially about mobile devices, Lync Phone Edition, and Mac clients.
Show Full Client extended info
Using -ShowTotal will also add additional info to the bottom section, including total number of users who are Lync enabled, total who are voice enabled, and percentage of total Lync enabled users who are connected.
ShowTotal option with totals and percentage
You can export the info to a .csv file for viewing/manipulation in Excel using:
Get-CsConnections.ps1 -pool [pool FQDN] -FilePath [path to csv file]
Installation
Execution Policy: Third-party PowerShell scripts may require that the PowerShell Execution Policy be set to either AllSigned, RemoteSigned, or Unrestricted. The default is Restricted, which prevents scripts – even code signed scripts – from running. For more information about setting your Execution Policy, see Using the Set-ExecutionPolicy Cmdlet.
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.
Download
v2.2 05-10-2013 Get-CsConnections.v2.2.zip
v2.1 12-13-2012 Get-CsConnections.v2.1.zip
v2.0 10-16-2012 Get-CsConnections.v2.0.zip
v1.9 09-21-2012 Get-CsConnections.v1.9.zip
v1.8 09-14-2012 Get-CsConnections.v1.8.zip
v1.7 Get-CsConnections.v1.7.zip
v1.6 Get-CsConnections.v1.6.zip
v1.4 Get-CsConnections.v1.4.zip
Changelog
See the changelog for a complete list of features added in each release
Thanks for the script. Is it possible to show which users/connections are connected via the edge server? Mainly on the -includeusers output if you could show FE Connection – Edge Connection that would be nice. Thanks
That’s on the wish list for that script.
Hi Pat,
Thank you for an excellent script.
Is it possible to show the hostname or IP address of the client connection? This would be a great help in tracking down machines running older client versions.
Thanks.
Hi John,
Yes, isn’t this a BRILLIANT script?!?!
I’m with you – a list of client IP addresses would be a VERY handy thing to have.
I’ve had a poke through the rtcDyn database but can’t find anything to ID the user’s endpoints. As Lync has an open connection to the client while you’re logged-in, it mightn’t even capture the hostname or IP.
I get some errors.. Only lists connections on the server where I run the script..
.\Get-CsConnections.ps1 -Pool lyncpool
Checking these pool servers in pool: lyncpool.VUC.NO
mslync01.vuc.no
mslync02.vuc.no
Exception calling “Open” with “0″ argument(s): “A network-related or instance-s
pecific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 26 – Error Locating Server/Instance Specified)”
At C:\Scripts\Get-CsConnections.ps1:181 char:18
+ $connection.Open <<<< ()
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Exception calling "Fill" with "1" argument(s): "A network-related or instance-s
pecific error occurred while establishing a connection to SQL Server. The serve
r was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 26 – Error Locating Server/Instance Specified)"
At C:\Scripts\Get-CsConnections.ps1:190 char:31
+ $recordcount=$sqladapter.Fill <<<
Svein – I haven’t forgotten about this. I’ve seen this a couple of times, and not been able to determine the issue. Still looking…
I have this same error.
When you open your PowerShell/Lync Management Shell session, make sure you start it as Administrator. In a couple of instances where I’ve seen the error, doing that seems to help.
That did not help when I was running it.
Hi! I got the same error on my pool where I have two FE servers. It will only list the users on the FE where I run the script. (and the result i 100 % connections)
I just want to say thanks for an awesome script. This gave me EXACTLY what a manager wanted to know.
Cheers!
Hi Pat, not sure if this helps but I receive the same SQL Exception error, but only on one server. It works fine on my other Front-End in the same pool – I’m using the Powershell as Administrator and using the same login credentials on both servers.
I don’t want to just post the negatives though! This script is fantastic and it’s helped me quite a bit thus far! Thanks very much.
Yeah, the problem is I’m not a SQL guy. I sometimes see this problem, and sometimes it goes away. Free beer to whoever figures it out!
I was gettign teh error mentioned above, so i changed the
#Define SQL Connection String
[string]$connstring = “server=$server\rtclocal;database=rtcdyn;trusted_connection=true;”
to
#Define SQL Connection String
[string]$connstring = “server=\;database=rtcdyn;trusted_connection=true;”
This got rid of that connection error, but now I get ‘Nothing returned from Query’. Perhaps that isn’t the right value to change if I want to hard code the SQL server? Any ideas on what would cause the ‘Nothing returned from Query’?
For some reason, what i changed it to didn’t post correctly.
lyncserver.domain.name\lync represent my SQL server & instance name
#Define SQL Connection String
[string]$connstring = “server=lyncserver.domain.name\lync;database=rtcdyn;trusted_connection=true;”
For those having issues, look at the comment block near the top of the script. Make sure you follow those steps, including firewall exceptions, and named pipes.
Bumped to v1.7 – sorting of server names is better; -ClientVersion no longer automatically triggers -IncludeUsers; minor code optimization. Also added some installation info to the blog post.
Still not sure what’s causing some to not work. I’m not a SQL guy, but open to suggestions.
Pat, tremendous script – many thanks! Can I make a suggestion? Have the script output all connections and their version in a table? That would be easier than asking the script to tell me every connection with a specific version. Cheers!
Yeah, I’ve got some ideas around that as well as targeting just specific servers within a pool. It gets real interesting when there are 15,000 people in a pool and you want to see the users.
However, I’m working on some nagging issues that some people have reported. Hoping to get those squared away soon.
Oh, one other thing about the Installation: the because RTCLOCAL is a named instance, and assuming you’ll be connecting via TCP/IP, not named pipes (because TCP/IP normally has a higher priority when the connection type isn’t specified), the firewall needs to allow UDP 1434 (not TCP), the SQL Browser service needs to be running (I’m guessing that this is the most common connection problem) and the port that the instance is actually listening on (which can be seen in the SQL config) also needs to be open on the firewall – or just allow SQL Server. Named pipes isn’t then needed. However, if the client uses named pipes, not TCP/IP, the SQL instance does use the default named pipe, so in theory the SQL Browser shouldn’t be needed, just File and Printer sharing in the firewall. The former method doesn’t require changing the SQL server config, so I’d generally go for that.
HTH
I just wanted to leave a short note to say thanks for creating this script, it is excellent!!
It’s a good job there are people like you around to help the respective community in these cases where Microsoft fall short.
Many thanks…
R.
I got an error when using -sipaddr: “Cannot find user in Active Directory with the following SAM account Name…” until I changed line 584 to :
$UserPreferredInfo = Get-CsUserPoolInfo -Identity sip:$SipAddress
Is the script working for Lync 2013?
Anyone using this successfully with Lync Server 2013? I get nothing returned when trying a 2013 pool or a 2013 server.
I don’t think it’s going to work on 2013. The database layout is different.
I modified lines 256-262 as follows to get the script to work with 2013:
$command.CommandText = “Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, RA.Fqdn `
From rtcdyn.dbo.RegistrarEndpoint RE `
Inner Join rtcdyn.dbo.Endpoint EP on RE.EndpointId = EP.EndpointId `
Inner Join rtc.dbo.Resource R on R.ResourceId = RE.OwnerId `
Inner Join rtcdyn.dbo.Registrar RA on EP.RegistrarId = RA.RegistrarId `
$whereClause `
Order By ClientVersion, UserName”
Hi,Pat
Does the rtcdyn database containing the user information from which edge server it connect to ?
If you get ideal the on which table in which column containing that information ,please share with me ,thanks
I am using a version of this script on Lync 2013 pool and putting the results into a csv but it only give users from one of the servers in the front end pool and returns no results from the other
This script isn’t meant for 2013. If you just do the console output, does it show connections for all pool servers?
Pat, that’s the interesting thing, I have 2 servers in my lync 2013 front end. It reports the # of users in the 2nd server spw099lnc04 but not in spw099lnc03 the first server….they were built the same time/way. See results. I have run the script from both front ends with the same result. See below. PS. 2013 control panel doesn’t tell you # of users on a server within the front end pool.
PS C:\tempscripts> .\Get-csconnections.ps1 -filepath C:\tempscripts\lync20130
2.csv
cmdlet Get-CsConnections.ps1 at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Pool: lyncfe2013
Checking these pool servers in pool: lyncfe2013.COMPUCOM.LOCAL
spw099lnc03.compucom.local
spw099lnc04.compucom.local
Iterate through Frontend pool collection
Working on server spw099lnc03.compucom.local
Query database ‘rtcdyn’ at ‘spw099lnc03.compucom.local\rtclocal’
No data returned!!
Working on server spw099lnc04.compucom.local
Query database ‘rtcdyn’ at ‘spw099lnc04.compucom.local\rtclocal’
$data length 51
Query Results written to C:\tempscripts\lync20130502.csv
Forgot to come back after I ran into similar issues in February. Since then I’ve been querying a Lync 2013 pool with minor modifications to the SQL in function Get-Data. I only use this with -Pool and -FilePath, so it’s possible that some other options do not work correctly.
Starting at line 256, replace with:
$command.CommandText = “Select (cast (RE.ClientApp as varchar (100))) as ClientVersion, R.UserAtHost as UserName, Reg.Fqdn `
From rtcdyn.dbo.RegistrarEndpoint RE `
Inner Join rtcdyn.dbo.Endpoint EP on RE.EndpointId = EP.EndpointId `
Inner Join rtc.dbo.Resource R on R.ResourceId = RE.OwnerId `
Inner Join rtcdyn.dbo.Registrar Reg on EP.RegistrarId = Reg.RegistrarId `
$whereClause `
Order By ClientVersion, UserName”
This changes the last field in the select list and the last join. Use at your own risk of course.
Darin,
Thanks for the fix. Works awesome with Lync 2013