Update 10/31/13 - Added an additional SQL query if you have the Monitoring Server available.
A question that comes up from customers from time to time is how do I get a list of what users are actually using OCS/Lync? While there's no built in report to easily tell you what users are actually signing into the environment, there is some information stored in SQL that you can use to help figure out adoption rate in your environment. The information we're looking for is stored in the LastNewRegisterTime column in the HomedResourceDynamic table in the rtcdyn database. You can run the following queries* to pull back the user's SIP URI and their last registration time:
For Lync Server 2010/2013
USE rtcdynSELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTimeFROM rtcdyn.dbo.HomedResourceDynamicINNER JOIN rtc.dbo.Resource on rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerIdINNER JOIN rtcdyn.dbo.RegistrarEndpoint ON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtcdyn.dbo.HomedResourceDynamic.OwnerIdWHERE IsServerSource = 0ORDER BY UserAtHost
Which produces the following output:
For OCS 2007 R2
USE rtcdynSELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTimeFROM rtcdyn.dbo.HomedResourceDynamicINNER JOIN rtc.dbo.Resource ON rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerIdORDER BY UserAtHost
Note: A user with a LastNewRegisterTime of NULL is a user that has never logged in, but has been added to someone's contact list. You would want to remove these entries from your exported results.
While the query to gather the information is very similar the SQL instance that you connect to to retrieve this information is different depending on which version you're using. In OCS 2007 R2 all of this information was stored in the rtcdyn database on the SQL instance that you defined when first creating the pool. Because of the changes to the registrar functionality in Lync Server 2010 to support survivability, the information that we're looking for has moved to the RTCLOCAL SQL instance that is stored on each registrar.
You can see this if you compare the HomedResourceDynamic table in the rtcdyn database between the Front End Pool SQL Instance and one of the Front End Server's RTCLOCAL SQL Instance:
Front End Pool SQL Instance
RTCLOCAL SQL Instance
For row 2, OwnerId 12 corresponds to my Lync test user that has logged into the client. You can see that the information that we're looking for, LastNewRegisterTime, only gets populated in the RTCLOCAL SQL instance on the Front End Server. So this means that in Lync Server 2010 you don't have one place you can go for all of the users homed on that pool. You will need to run the query and aggregate the data from every registrar in the pool, as well as any SBA/SBS deployed. The other issue that comes up in an Enterprise Edition pool with multiple Front End Servers is that when users fail-over to another Front End Server in the pool, there is a record created in that Front End Server's RTCLOCAL rtcdyn database. After you run the queries and have exported all of the results you would want to clean up the duplicate entries so that you weren't reporting inflated numbers.
Even though there's a little work involved in gathering the information this is a fairly easy way to gauge adoption and see which of your users are actually using the OCS/Lync.
Using the Lync Server 2013 Monitoring Server
If you have the Monitoring Server role configured in your environment, and for Lync Server 2013 everyone should!, you can use information contained in the LcsCDR database to pull back the last time a user signed in. You can run the following query* to pull back the user's SIP URI and their last login time:
USE LcsCDRSELECT dbo.Users.UserUri, dbo.UserStatistics.LastLogInTimeFROM dbo.UserStatisticsJOIN dbo.Users ON dbo.Users.UserId = dbo.UserStatistics.UserIdORDER BY UserUri
The advantage to using the Monitoring Server to obtain this data is that unlike the information contained in the rtcdyn database, the information from the LcsCDR data will persist even when the user isn't signed into Lync.
*These queries are provided for you to use at your own risk. Please make sure you test before running in a production environment.
Exactly what I was looking for, Thanks a lot!!
what permissions do i need to access RTCLOCAL rtcdyn database?
Being a member of RTCUniversalServerAdmins should give you the rights needed to read the rtcdyn database on the local Front End Servers.
OK. Now i am able to see list of tables, but i still get error when trying to select.
The SELECT permission was denied on the object 'RegistrarEndpoint', database 'rtcdyn', schema 'dbo'.
We have multiple domains in our Lync environment. How would I pull this query for a single domain?
Run as administrator SSMS solved my problem. Thank you anyway.
Glad you got it working!
If you modify the WHERE clause like this:
WHERE (IsServerSource = 0 AND UserAtHost LIKE '%@domain.com')
and replace domain.com with the SIP domain you're looking for, that should work.
Perfect. That's exactly what I needed. Many thanks. Now how about a way to eliminate duplicates? I suspect the duplicate logins are for their devices. They log in both with the Lync client and CX 600s.
Also, how far back should this go? Not seeing anything beyond the last two weeks.
The duplicates are users logged into multiple endpoints. Since this data is stored in the rtcdyn database, it's only going to show you currently logged in users. Also remember that if a user signs into a new endpoint the LastNewRegisterTime is updated for all logged in endpoints for that user.
I ran your query for Lync 2010, and the query executes successfully, but returns no users which I know is not correct.
Is there any way to get Monitoring Server to generate reports based on use activity e.g. (Which user did Conferencing/ Application sharing ..etc) and between what time and how long?
If there's nothing like that, is there any third party or way to do it ?
There isn't a built in report that does exactally what you want, but all that information is essentially in the databases. You would just need to write your own report to pull out the information that you're looking for.
this takes doug's concept ( blogs.technet.com/.../how-to-get-the-last
i have FE and Edge server . i tried to find the .UserAtHost but i couldn't find it
the command go in debugging process without getting any data
can you please tell me exactly where in DB can i find the client activity