Auditing in MS Dynamics 2011 CRM is pretty comprehensive. But whilst a load of information is audited, making sense of it is a lot more difficult.

Assuming that you have turned on Auditing (go to Settings > System  > Auditing in the left hand navigation, then choose Global Audit Settings and ensure you have started auditing and are auditing user access) then you will see that the Audit Summary View quickly fills up with data. But if you need to do some simple analysis on this audit data, you’ll find you can’t. You can filter the view, but you can’t export it to Excel for further analysis not from the view directly, nor from advanced find. It remains  a great mystery to me why some views in Dynamics can’t be exported and this is a classic case where this would be extremely useful.

In my case, I just wanted to find out when users had last been using the system. I found this article CRM 2011 Reports on Data Auditing which looked promising, but I couldn’t get it to run.

Turns out the audit table, whilst it is very denormalised does has some useful information that you can easily grab from it. So assuming you have access to the SQL database (sorry if you are using hosted) this simple SQL script will tell you who has been last using your MS Dynamics system.

Switch to the CRM database of your Organization:

use MSCRM_ORGANIZATION
go

and then run the following SQL, which finds the most recent audited activity for each user:

select MAX(a.CreatedOn) as lastlogon, UserIDName
from Audit a
group by UserIdName
order by lastlogon desc
go