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:
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
The query outputs the systemuser name, the organisation name and the date and time of the last login:
username@exaple.com OrganisationName 2011-03-23 16:36:13.457 You can finetune the query to your own wishes.
Hosk's Dynamic Blog 
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
LikeLike
You can get the login name by joining to the specific organisation database you are interested in and using the SystemUser view.
SELECT
AuthInfo, O.FriendlyName, SUO.LastAccessTime, SU.FullName, SU.FirstName, SU.LastName, SU.BusinessUnitId, SUO.CrmUserId, SUO.OrganizationId
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
JOIN INSERTORGANISATIONNAMEHERE_MSCRM.dbo.SystemUser SU ON SU.SystemUserId=SUO.CrmUserId and SU.OrganizationId=SUO.OrganizationId
WHERE
LastAccessTime IS NOT NULL
ORDER BY SUO.LastAccessTime DESC
Fergus
LikeLike
Great work Ben. Doesn’t look like it will work with 4.0. The LastAccessTime from the table.
Mitch
LikeLike
Hey, Is there any way to get the IP address of the logged on user when logging??
LikeLike
I haven’t found a way to do that yet. I think they only store the active directory name
LikeLike
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
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
LikeLike
very interesting but what about OnLine version?
How can I query last user login if I cannot query in the config DB?
thank you
LikeLike
perhaps you could turn on User auditing otherwise I’m not sure you can do this
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
hi
last access column time is null in SystemUserOrganizations table
please help to solve this.
LikeLike