Share via

How to Get a List of Client Versions and the Users Logged into Them

  • Article

Another question that comes up from customers, especially when we're talking about migrations and updating clients, is how do I figure out which users are running the old version of the client?  You can run the following queries* to pull back the user's SIP URI and the client version that they're signed into:

For Lync Server 2010/2013 & Skype for Business 2015

USE rtcdyn
SELECT COUNT(*) as Occurrences,
CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100)) as 'Client Version'
FROM rtcdyn.dbo.RegistrarEndpoint
WHERE IsServerSource = 0
GROUP BY CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100))
ORDER BY CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100))

SELECT rtc.dbo.Resource.UserAtHost as 'SIP Address', CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100)) as 'Client Version'
FROM rtcdyn.dbo.RegistrarEndpoint
INNER JOIN rtc.dbo.Resource
ON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtc.dbo.Resource.ResourceId
WHERE IsServerSource = 0

For Lync/Skype for Business this query needs to be run against each local registrar's SQL instance (RTCLOCAL).

For OCS 2007 R2

USE rtcdyn
SELECT COUNT(*) as Occurrences,
CAST(rtcdyn.dbo.Endpoint.ClientApp as varchar(100)) as 'Client Version'
FROM rtcdyn.dbo.Endpoint
WHERE IsServerSource = 0
GROUP BY CAST(rtcdyn.dbo.Endpoint.ClientApp as varchar(100))
ORDER BY CAST(rtcdyn.dbo.Endpoint.ClientApp as varchar(100))

SELECT rtc.dbo.Resource.UserAtHost as 'SIP Address', CAST(rtcdyn.dbo.Endpoint.ClientApp as varchar(100)) as 'Client Version'
FROM rtcdyn.dbo.Endpoint
INNER JOIN rtc.dbo.Resource
ON rtcdyn.dbo.Endpoint.OwnerId = rtc.dbo.Resource.ResourceId
WHERE IsServerSource = 0

NOTE: Make sure that when you copy/paste the queries above, you make sure that the formatting is the same as above.  If the query pastes in all on one line, it will more than likely fail.

 

Both queries produce the following output:

The first query returns each client version that a user is currently signed into and the number of occurrences of each version.  This is very similar to the client version summary query on the database tab in the OCS Management Console:

The second query will list each user's SIP URI and the associated client version.  In the example above, Jeff Wallace is signed into two endpoints and each one is listed separately.  These queries are very useful to make sure that users are using the latest version of the client and if not, which users need to be updated.  The important thing to remember is that this data is only stored for users that are currently signed into OCS/Lync, so they will only give you a point in time snapshot of your environment.

 

*These queries are provided for you to use at your own risk. Please make sure you test before running in a production environment.

Comments

  • Anonymous
    January 01, 2003
    @Ed.Carden I would probably go about this a little differently.  Instead of trying to dig through the database and pull out the presence information, I would look at an MSPL script on the Front End Servers that parses the SIP SERVICE message sent for presence updates and log it out to a database.  We don't document the schema of the rtc databases.

  • Anonymous
    January 01, 2003
    Is there a way to output the second part with the name and client version to an excel spreadsheet?

  • Anonymous
    January 01, 2003
    @AnonymousThe easiest way would be to just right click on the results and select "Copy with Headers" and then paste that into Excel.

  • Anonymous
    January 01, 2003
    @Korbyn You are correct that there really isn't any client version tracking built into the product.  It really has to do with the fact that this data is only stored in the rtcdyn database and therefore is only available when the user is logged in.  There's nothing stopping you from writing a MSPL script to pull this information out into a database.  Also, depending on why you want to know what version the user is using, you could look at something like the client version filter to allow or block certain versions from connecting as well as forcing an update to the latest version of the client.

  • Anonymous
    January 01, 2003
    Fantastically usefull, but I'm guessing there's no tracking based on last client used when they last signed in, or even from multiple endpoints.  Of course if we has a software managment system, I wouldn't have to worry about this... Guess MS needed to save something for future releases.  Client tracking and Phone number inventory tracking could be greatly improved...

  • Anonymous
    January 01, 2003
    Just recently had an issue where an android device was listed but not logged in and wouldn't purge out. Supposed to automatically after what 15 mins, but wasn't. A script or tool for getting a list of user connections and then somehow purging one or all endpoints, or forcing them off so they have to be resigned in, would have been handy.

  • Anonymous
    January 01, 2003
    @TraciH The query already works with Lync Server 2013.  I would make sure that you have permissions to the SQL Express instance.

  • Anonymous
    July 14, 2011
    The comment has been removed

  • Anonymous
    October 18, 2011
    Apologies’ if this is a bit off topic but your post closely resembles what I'm looking for, how to query LYNC DB's via T-SQL to obtain info.   I tried to decipher the LNYC DB's myself before I found out how many different DB's it uses and how many different SQL Server instances as well.  It’s like spaghetti code but for data storage. My overall goal is to get something like a Data Dictionary for the LYNC databases.  The more immediate need is to find a way to get User Presence info and when that info changes, to report on when users went from active to inactive and for how long.  If each change in User States (from Active to Inactive to Away and so on) is recorded then all I need is the SQL Server Instance + Database+ Table.Columns that this data is stored and I can construct the proper logic to get that info.  The hard part is finding where the heck LYNC keeps that data. Any suggestions on links for  "LYNC for SQL Developers" types would be most appreciated as well.

  • Anonymous
    September 25, 2013
    The comment has been removed

  • Anonymous
    October 24, 2013
    I want to get a list of users who are using the CWA client, is that possible?

  • Anonymous
    October 25, 2013
    @I Frenken Users using the CWA client should show up with a client version that denotes CWA, so it should be fairly easy to tell which users are using the CWA client.

  • Anonymous
    October 29, 2013
    Hi Dodeitte, That is true, I would expect them to do. I would expect RTCC/3.5.0.0 CWA/3.5.0.0 in the export. But this does not happen, I only get UCCAPI and UCCP clients. So I think the query does not get everything. Do you know where I can find the logged in CWA clients then?

  • Anonymous
    October 31, 2013
    The comment has been removed

  • Anonymous
    November 01, 2013
    @ dodeitte Thanks that is what I was looking for !!!

  • Anonymous
    June 07, 2014
    There is also a PowerShell script that shows you this data in several different ways:http://www.ehloworld.com/269

  • Anonymous
    March 09, 2016
    When I run the query on a Lync 2013 SQL backend I get 'rtcdyn' does not exist. I have Enterprise version of Lync 2013.

    • Anonymous
      March 19, 2016
      This query needs to be ran on the Front End Servers in the pool.