Staff who have actively used CRM in the past 30 days

Staff who have actively used MS CRM in the past 30 days 

(Please leave your feedback if you find this useful)

In order to have a list of users who logged into CRM in the last 30 days, we should start by having a look at the audit history of a user in CRM:

As you can see the Audit history shows the last login date.
So all we have to do is to run a select on audit records to retrieve the last month’s “ObjectId” and name of the users.
 Here is the select statement:

SELECT dbo.Audit.ObjectId, dbo.FilteredSystemUser.fullname,  dbo.FilteredSystemUser.isdisabledname,

MAX(DATEADD(HOUR,10,dbo.Audit.CreatedOn)) as [Last Date Accessed]

FROM            dbo.Audit WITH (nolock) INNER JOIN

                         dbo.FilteredSystemUser  (nolock) ON dbo.Audit.ObjectId = dbo.FilteredSystemUser.systemuserid

WHERE        (dbo.Audit.Action = 64) AND (dbo.Audit.CreatedOn >= dateadd(m, -1, getdate()) )

group by dbo.Audit.ObjectId, dbo.FilteredSystemUser.fullname, dbo.FilteredSystemUser.jobtitle, dbo.FilteredSystemUser.isdisabledname

Order by dbo.FilteredSystemUser.fullname