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
USE rtcdynSELECT COUNT(*) as Occurrences,CAST(rtcdyn.dbo.RegistrarEndpoint.ClientApp as varchar(100)) as 'Client Version'from rtcdyn.dbo.RegistrarEndpointWHERE IsServerSource = 0group 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.RegistrarEndpointINNER JOIN rtc.dbo.ResourceON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtc.dbo.Resource.ResourceIdWHERE IsServerSource = 0
For Lync this query needs to be run against each local registrar's SQL instance (RTCLOCAL).
For OCS 2007 R2
USE rtcdynSELECT COUNT(*) as Occurrences,CAST(rtcdyn.dbo.Endpoint.ClientApp as varchar(100)) as 'Client Version'from rtcdyn.dbo.EndpointWHERE IsServerSource = 0group 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.EndpointINNER JOIN rtc.dbo.ResourceON rtcdyn.dbo.Endpoint.OwnerId = rtc.dbo.Resource.ResourceIdWHERE IsServerSource = 0
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.
High Five! Keep up the good work man.
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...
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.
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.
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.
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.
The query already works with Lync Server 2013. I would make sure that you have permissions to the SQL Express instance.
I want to get a list of users who are using the CWA client, is that possible?
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.
That is true, I would expect them to do. I would expect RTCC/18.104.22.168 CWA/22.214.171.124 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?
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.
Thanks that is what I was looking for !!!
Is there a way to output the second part with the name and client version to an excel spreadsheet?
The easiest way would be to just right click on the results and select "Copy with Headers" and then paste that into Excel.
There is also a PowerShell script that shows you this data in several different ways: