Doug Deitterick's Blog

Information about Microsoft Lync, OCS, and Exchange UM.

How to Get the Last Time a User Registered with a Front End

How to Get the Last Time a User Registered with a Front End

  • Comments 27
  • Likes

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 rtcdyn
SELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTime
FROM rtcdyn.dbo.HomedResourceDynamic
INNER JOIN rtc.dbo.Resource on rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
INNER JOIN rtcdyn.dbo.RegistrarEndpoint ON rtcdyn.dbo.RegistrarEndpoint.OwnerId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
WHERE IsServerSource = 0
ORDER BY UserAtHost

Which produces the following output:

For OCS 2007 R2

USE rtcdyn
SELECT rtc.dbo.Resource.UserAtHost, rtcdyn.dbo.HomedResourceDynamic.LastNewRegisterTime
FROM rtcdyn.dbo.HomedResourceDynamic
INNER JOIN rtc.dbo.Resource ON rtc.dbo.Resource.ResourceId = rtcdyn.dbo.HomedResourceDynamic.OwnerId
ORDER BY UserAtHost

Which produces the following output:


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 LcsCDR
SELECT dbo.Users.UserUri, dbo.UserStatistics.LastLogInTime
FROM dbo.UserStatistics
JOIN dbo.Users ON dbo.Users.UserId = dbo.UserStatistics.UserId
ORDER BY UserUri

Which produces the following output:

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.

Comments
  • Exactly what I was looking for, Thanks a lot!!

  • Hello,

    what permissions do i need to access RTCLOCAL rtcdyn database?

  • @Arturas Rimonis

    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.

  • @Arturas Rimonis

    Glad you got it working!

  • @Didier

    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.

  • @dodeitte

    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.

  • @Didier

    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 ?

    Thanks

  • @moh10ly

    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

    Thanks

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment