CRM 2011 & CRM 2013–Usage Audit Report (On-Premise Deployment)


For On-Premise deployment of CRM 2011 or CRM 2013, I have written a SQL Query which will give details about who when accessed the CRM and what was his/her role in CRM
CRM does have a Audit Summary View which let you see this details but you cannot export this data from CRM Audit Summary entity neither can you have carts/graphs around that data in CRM Report.
Here is script, run it as db_owner on CRM Database. You can also write SQL Connection in Excel and pull this data dynamically.

SELECT Usage.*

,(SELECT SecRole.Name +‘,’FROM SystemUserRoles SysUser

INNERJOINRole SecRole ON SysUser.RoleId = SecRole.RoleId

INNERJOIN SystemUse SU ON SysUser.SystemUserId = SU.SystemUserId

WHERE SU.DomainName = Usage.UserName

FORXMLPATH ())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

INNERJOIN SystemUser U ON A.objectid = U.Systemuserid

WHEREAction= 64

GROUPBY

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

Hope you find this script handy.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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