[LYNC/SFB]마지막 로그인 성공 시간 쿼리

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.