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


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

  1. dodeitte says:

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

  2. Run as administrator SSMS solved my problem. Thank you anyway.

  3. dodeitte says:

    @MMSM

    UserAtHost is a column in the Resource table in the rtc database in the RTCLOCAL instance on the Front End Server.  Make sure that you run this query from the Front End Server if you're running Lync Server 2010 or from the SQL Server if you're running OCS 2007 R2.

  4. dodeitte says:

    @Arturas Rimonis

    Glad you got it working!

  5. dodeitte says:

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

  6. moh10ly says:

    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

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

  8. Anonymous says:

    I have run this Query against Lync Server, in result I didnt get anything .. Any Idea Why ??

  9. dodeitte says:

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

  10. dodeitte says:

    @Arturas Rimonis

    Being a member of RTCUniversalServerAdmins should give you the rights needed to read the rtcdyn database on the local Front End Servers.

  11. Hello,

    what permissions do i need to access RTCLOCAL rtcdyn database?

  12. Anonymous says:

    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

  13. Rakesh says:

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

  14. Didier says:

    We have multiple domains in our Lync environment.  How would I pull this query for a single domain?

  15. Didier says:

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

  16. Jason Carter says:

    I ran your query for Lync 2010, and the query executes successfully, but returns no users which I know is not correct.

  17. Igor says:

    According to my tests the query shows values only for users currently registered on the pool.

    As soon as user signs out the record is purged from RTCDYN database, so the query can be used only for reporting for currently registered users and it will not give any info on users that signed out say a minute ago.

  18. Dan Sheehan says:

    I have over the entries (or lack thereof) found in these local SQL database on the 2010 front-end servers. I found my SIP Address when I ran my first query, but every query run since then I am no where to be found.

    So what would cause an entry to be removed from these databases and how long are these entries kept in the database? I.E. Why did my record disappear and also can I find records from 6 months ago?

    Thanks for your insight (and the query)

  19. Dan Sheehan says:

    Never mind – I found the answer in one of your replies: "Since this data is stored in the rtcdyn database, it's only going to show you currently logged in users."

    I had logged out which is why I disappeared from the list.

    Is there no database anywhere that records this type of last logon information? This is crazy we can't track this information in Lync 2010.

  20. Dan Sheehan says:

    Sorry for the repeated posts. I found this query:

    select res.UserAtHost as "SIP Address", hud.LastNewRegisterTime as "Last Logon" from rtcdyn.dbo.HomedResourceDynamic hud join

    (Select ResourceId, UserAtHost from rtc.dbo.Resource

    group by ResourceId, UserAtHost)

    res

    on hud.OwnerId=res.ResourceId

    order by "Last Logon"

    From: mikestacy.typepad.com/…/sql

    And after querying our multiple Front-End servers it seems to work.

    I knew there had to be a way if the client could show you the LastActive information for your contacts, even those offline.

  21. Dan Sheehan says:

    As payment for my repeated posts, I wanted to share that I turned this into a PowerShell script that can be run very quickly and pull in the last logon time and date for a user, including which client they used, and export it to a CSV file. The script always records the latest time and date for a user even if they have multiple entries across multiple Lync servers:

    gallery.technet.microsoft.com/Generate-Lync-user-last-afa68c72

  22. Steve says:

    Doug – is there an updated version for Lync 2013? Those database fields have been 'moved' somewhere I can't find…

  23. dodeitte says:

    @Steve

    The first query works for Lync Server 2013.  The rtcdyn database is in the RTCLOCAL instance on the Front End Servers.

  24. Rafael_G says:

    un query que me muestre los usuarios registrados por dia u hora

  25. Ramesh says:

    Hi,

    Could you pls help how i can fetch this information from OCS 2007 ?
    I was trying to modify SQL verify for OCS2007, but unfortunately rtcdyn.dbo.HomedResourceDynamic table/view doesn’t exist. There is a table rtcdyn.dbo.HomedResourceDynamic but if i use this table instead, SQL query fails.

  26. Ramesh says:

    Please read it as below
    *modify SQL query for OCS2007

  27. Ybenben says:

    Does anyone required to calculate the connecting time ?
    Last login time and last logoff time
    I s there a data on the logoff time?
    Thanks

  28. funkytechmonkey says:

    Anyone know how to do this with Lync Online? o365?

  29. Ed Swindelles says:

    @Doug

    We have some users who appear offline for a long period of time, say 100+ days. In the Lync address book it’ll say "Offline 152 Days", for example. I have yet to find out where Lync is pulling this specific piece of information. None of the SQL queries I’ve
    found are able to align with the data that the address book is reporting. Thanks!

  30. Baaskar R says:

    Still valid and thanks for your post…,