Tag: CRM Usage

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.