Dynamics CRM Audit & User Access Data

 

This article is to provide you with the different options you have in Dynamics CRM to get data on user access to the application, and when.

You have the ability to audit a user's logon access to the CRM Server. The information that is recorded includes when the user started accessing Dynamics CRM and if access originated from the Dynamics CRM Web application, Dynamics CRM for Outlook or SDK calls to the web services.

The following steps describe how to enable auditing of user logon access.

Click on Settings – Auditing – Global Audit Settings and on the Auditing tab check the boxes for “Start Auditing” and “Audit User Access”.

 

Now if you go to Settings – Auditing – Audit Summary view, you can view all the details of who logged into CRM system, such as like:

 

The link below also explains on how to audit user access in CRM using SDK

Audit user access

Your other option for deeper user access information is to use IIS logging, as would do for any .NET application, and information on this  with CRM can be seen here

Below is a script I have taken, and edited for CRM and you would run it in SQL as db_owner on the CRM database.

This SQL query will give you details of who and when accessed CRM and what his his/ her role in CRM.

You can export this to a report too if you wish.

You can also write SQL Connection in Excel and pull this data dynamically.

 

SELECT Usage.*

,(SELECT SecRole.Name +','FROM SystemUserRoles SysUser

INNER JOIN Role SecRole ON SysUser.RoleId = SecRole.RoleId

INNER JOIN SystemUser SU ON SysUser.SystemUserId = SU.SystemUserId

WHERE SU.DomainName = Usage.UserName

FOR XML PATH (''))AS SecRoleNames

FROM

(

SELECT U.fullname AS [FullName], U.DomainName AS [UserName]

,DATEPART(YYYY,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Year]

,DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Month]

,DATEPART(DD,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Day]

,COUNT(*) [Counter]

,MIN(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) [FirstAccessAt]

,MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) [LastAccessAt]

FROM AUDIT A

INNER JOIN SystemUser U ON A.objectid = U.Systemuserid

WHERE Action= 64

GROUP BY

U.fullname, U.DomainName

,DATEPART(YYYY,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))

,DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))

,DATEPART(DD,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))

) Usage

Results Example:

 

Best Regards

EMEA Dynamics CRM Support Team

 

Share this Blog Article on Twitter

Tweet

Follow Us on Twitter

Follow @MSDynCRMSupport