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

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

  1. dodeitte says:


    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.

  2. Anonymous says:

    Is there a way to output the second part with the name and client version to an excel spreadsheet?

  3. dodeitte says:


    The easiest way would be to just right click on the results and select “Copy with Headers” and then paste that into Excel.

  4. dodeitte says:


    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.

  5. Korbyn says:

    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…

  6. Korbyn says:

    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.

  7. dodeitte says:


    The query already works with Lync Server 2013.  I would make sure that you have permissions to the SQL Express instance.

  8. High Five! Keep up the good work man.

  9. Ed.Carden says:

    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.

  10. TraciH says:

    Any chance you can update this for Lync 2013?  I think they changed something because when I run your script I get access is denied to run SELECT.

    Msg 229, Level 14, State 5, Line 2

    The SELECT permission was denied on the object 'RegistrarEndpoint', database 'rtcdyn', schema 'dbo'.

    Msg 229, Level 14, State 5, Line 9

    The SELECT permission was denied on the object 'Resource', database 'rtc', schema 'dbo'.

    I am running it against the local SQL express RTCDYN database on my front end server.

  11. I Frenken says:

    I want to get a list of users who are using the CWA client, is that possible?

  12. dodeitte says:

    @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.

  13. I Frenken says:

    Hi Dodeitte,

    That is true, I would expect them to do. I would expect RTCC/ CWA/ 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?

  14. dodeitte says:

    @I Frenken

    When you run the SQL query, remove the following line from both SELECT statements:

    WHERE IsServerSource = 0

    That line filters out server-based applications, but it appears that the CWA clients fall under that category, which kind of makes sense.

  15. I Frenken says:

    @ dodeitte

    Thanks that is what I was looking for !!!

  16. Pat Richard says:

    There is also a PowerShell script that shows you this data in several different ways:

  17. Jason Meyer says:

    When I run the query on a Lync 2013 SQL backend I get ‘rtcdyn’ does not exist. I have Enterprise version of Lync 2013.

    1. dodeitte says:

      This query needs to be ran on the Front End Servers in the pool.