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.