Tuesday, October 4, 2011

When was the last time a user logged in?

Recently, I was asked to look into which users where using our production MSCRM 2011 environment. Prior to MSCRM 2011, you could get a rough idea of which users where accessing CRM by looking through IIS logs. However, this was tedious and it was not very accurate, since the Outlook Client traffic is included in the IIS logs. Luckily, Microsoft made life easier with CRM 2011 and included a column named LastAccessTime in the SystemUserOrganizations table of the MSCRM_CONFIG database. To get the LastAccessTime for your users, you can run the query below from your Organization database, #ORGNAME#_MSCRM.
SELECT
     SU.SystemUserId
     ,SU.DomainName
     ,SU.FullName
     ,SUO.LastAccessTime
FROM SystemUser SU
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] SUO ON SUO.CrmUserId = SU.SystemUserId
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserAuthentication] SUA ON SUA.UserId = SUO.UserId
ORDER BY SU.DomainName
I want to credit Results on Demand who posted the query that inspired mine. You can see their version of the query that runs from the MSCRM_CONFIG database at http://www.resultondemand.nl/support/blog/post/How-to-get-the-last-login-date-and-time-of-a-CRM-user-(MSCRM-2011).aspx

No comments:

Post a Comment