CRM 2011 – How to get the Last Login Date and Time of a CRM Instance

today I found myself wondering if I check when a CRM instance was last used.  We have a bunch of CRM instances and I was wondering when or if they were being used.

I was a bit dissappointed not to find anything within CRM 2011 but then I found this article which had a piece of SQL code which could do the trick.  I guess this would probably work for CRM 4.

You need to run the query against the MSCRM_CONFIG database.   When I ran the query it returned null for the user name but it did return the time .  You will have to join a few more tables to get the user name.

In Microsoft Dynamics CRM 2011 you can get the last login time of a CRM user by using the below query:


AuthInfo, O.FriendlyName, SUO.LastAccessTime


SystemUserOrganizations SUO

LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId AND LEFT(AuthInfo, 1)=’C’

LEFT JOIN Organization O ON SUO.OrganizationId=O.Id


LastAccessTime IS NOT NULL


The query outputs the systemuser name, the organisation name and the date and time of the last login: OrganisationName 2011-03-23 16:36:13.457

You can finetune the query to your own wishes.

12 thoughts on “CRM 2011 – How to get the Last Login Date and Time of a CRM Instance

  1. 56 May 24, 2011 / 8:53 pm

    Could anyone help me on the join to just get the Person’s name of the last login. This is good but I can’t get it to work to find out who the actual user is, no matter how hard I try. Any recommendations on which other tables I can join to get the User’s name. Thanks.

    SELECT AuthInfo,O.FriendlyName, SUO.LastAccessTime
    FROM SystemUserOrganizations SUO
    LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId AND LEFT(AuthInfo, 1)=’C’
    LEFT JOIN Organization O ON SUO.OrganizationId=O.Id
    WHERE LastAccessTime IS NOT NULL
    ORDER BY SUO.LastAccessTime DESC


  2. Fergus Prone June 16, 2011 / 8:36 am

    You can get the login name by joining to the specific organisation database you are interested in and using the SystemUser view.

    AuthInfo, O.FriendlyName, SUO.LastAccessTime, SU.FullName, SU.FirstName, SU.LastName, SU.BusinessUnitId, SUO.CrmUserId, SUO.OrganizationId


    SystemUserOrganizations SUO

    LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId AND LEFT(AuthInfo, 1)= ‘C’

    LEFT JOIN Organization O ON SUO.OrganizationId=O.Id

    JOIN INSERTORGANISATIONNAMEHERE_MSCRM.dbo.SystemUser SU ON SU.SystemUserId=SUO.CrmUserId and SU.OrganizationId=SUO.OrganizationId


    LastAccessTime IS NOT NULL

    ORDER BY SUO.LastAccessTime DESC



  3. Mitch Milam July 17, 2011 / 9:36 pm

    Great work Ben. Doesn’t look like it will work with 4.0. The LastAccessTime from the table.



  4. Osama September 29, 2011 / 8:07 am

    Hey, Is there any way to get the IP address of the logged on user when logging??


    • Hosk September 29, 2011 / 9:39 am

      I haven’t found a way to do that yet. I think they only store the active directory name


  5. MsCrmKing October 4, 2011 / 6:56 pm

    If you configure\setup IIS to log to SQL, which will take a some work. You should be able to join the tables and get the IP of the user. Also if you want to run to the LastAccessTime while running the query from the Organization Database #ORGNAME#_MSCRM you can use the following

    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


  6. gabriele March 8, 2012 / 4:14 pm

    very interesting but what about OnLine version?
    How can I query last user login if I cannot query in the config DB?
    thank you


  7. Hosk March 8, 2012 / 4:16 pm

    perhaps you could turn on User auditing otherwise I’m not sure you can do this


  8. dan floyd October 17, 2012 / 9:22 pm

    Is there a way to take that query and expose it as some sore of dynamics crm system view? I prefer a view over a report so I can drill into the records more easily and possibly use the results w/workflows. Thanks.


  9. David September 17, 2013 / 4:15 pm

    I came across this post and was interested in the reply by Fergus Prone.
    I ws wondering if you could tell me the significance of the LEFT(AuthInfo, 1)= ‘C’ on the join.
    Mainly why records not starting with C are ignored?

    I have seen a lot of SystemUserAuthentication.AuthInfo fields containing values starting W:s-1-5-21…… and am trying to find out what this means.

    Thanks in advance.


  10. aladdin bail bonds June 13, 2014 / 10:07 pm

    Hey I know this is off topic but I was wondering
    if you knew of any widgets I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and
    was hoping maybe you would have some experience with something like this.
    Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.


  11. svenbinion24215 February 2, 2016 / 12:19 am

    Now they are ahead of the game and work to predict what is being developed and develop testing for it. Depression is medical condition that affects people of all genders, races, ages, and income levels. And the drug, Provigil, is prescribed only for people with narcolepsy.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s