I got asked this question today about auditing and some dodgy values, which lead me to look at auditing in CRM.
If you want a quick overview of auditing read this article CRM 2013 – Quick Overview of Auditing
A very quick recap, Auditing can be set at three levels
The global setting turns auditing on/off. You can then set auditing on/off for individual entities and individual fields.
You can audit all fields apart from a few system ones.
Funny Looking Icon
Looking at the audit logs I could see an odd icon in the new value column, it looked a bit like a broken basket or maybe a small robot from star wars (image taken from this forum post – MS Dynamics CRM: Audit history shows icons instead of values
This cause of this icon is because auditing has been stopped and started again either for the entity or the fields.
For reasons I don’t quite understand when we import a managed solution it often seems to turn auditing off. I’m guessing this is a CRM 2013 bug of some kind but I can see in the audit logs the message
Entity audit stopped
What seems to happen when we import a managed solution in CRM 2013 SP1 it’s temporarily turning off auditing.
The behaviour is discussed in this forum post
CRM 2011: when auditing is turned off, why do existing Audit records lose “New Value”?
This excellent blog post explained the issue in more detail
A minimalist approach to Managed Solutions in CRM
Auditing (and some other fields) is a checkbox which is either ticked on/off and this value is recorded in your managed solution. I’m not 100 percent sure imported a managed solution effects the auditing, it doesn’t make sense to import auditing settings because the settings can be changed by users and would be overwritten each time you imported your managed solution.
Something odd happens when we import a managed solution but as yet I’m not sure what it is, if it’s turning fields on or off or if it’s just turning auditing on and off.
How Auditing works
The mighty Scott Durow explained how Auditing data is stored in CRM in this forum post CRM 2011 – Reporting on Audits
2) The Audit view only provides you easy access to the transaction details (user, record, change type, date etc) – to get the attribute change data, you must ‘unpack’ the ‘ChangeData’ field using the ‘AttributeMask’ field. The ChangeData is a ~ separated list of *previous* values. The Attribute Mask is a comma separated list of attribute numbers (as defined by the ColumnNumber field of the [MetadataSchema].[Attribute] table). The difficult bit is that although you can get the ‘previous value’ by parsing these fields, to get the ‘new value’ you must get the next audit entry for the particular record or if there is no next audit entry, you must get the values that are currently stored in the Entity Record. Further more, if it references option set values, you need to lookup the option set value that was valid on that date, and any entity references you will need to lookup the display name since the values only give you the type and id. The SDK Webservices does all this hard work in code rather through SQL and so it will be the easiest to use, however, it isn’t optimized for reporting.
To take the relevant parts out
Audit table’s only stored the previously changed value
The current value/new value of a record has to be retrieved from the next audit log or the current field value.
So it seems the audit views we use are a mixture of audit records, mixed in with the current field value, well packaged so we think all the information is in the audit log.
This is a problem for reports but if you retrieve audit records via the CRM SDK it gets you the all the data, another reason to use CRM SDK rather than direct SQL queries in code.
Can’t view Audit logs
There are some security roles associated with auditing and if you can’t view the audit logs you need to get some audit security privileges.
View Audit History
View Audit Summary
View Audit Partitions
Delete Audit Partitions
View Audit History is needed to view the audit history for individual entities.
View Audit Summary is needed to view the audit summary page in the auditing area.
The audit partition security roles are to do with viewing and tidying up the partitions, which is to do with tidying audit logs up and removing some auditing data which is old.
This blog posts walks you through setting up your audit privileges
Enabling Viewing of CRM Audit Records
Be aware field level security fields cannot be audited according to the CRM SDK article – Field security entities
Field level security applies to everything
It applies to all components, such as the Microsoft Dynamics CRM SDK, reports, search, offline, filtered views, auditing, and duplicate detection. For this release, field security can be applied to both custom fields and many out-of-box (OOB) fields.
For a run through of field level security go to this blog post CRM 2013 – How to set up Field Level Security
Auditing User access
Auditing allows you to audit when users have logged on to CRM, CRM SDK Audit Access Link
There are a couple of interesting things I learnt from the CRM SDK article
The frequency of auditing user access can be read or set using the Organization.UserAccessAuditingInterval attribute. The default attribute value of 4 indicates user access is audited once every 4 hours.
Which makes me wonder how exactly it audits user access?
The other interesting part of user auditing is it can tell how the user logged on to CRM
UserAccessviaWeb indicates the user accessed CRM via Outlook client or Web Application
UserAccessViaWebServices indicates access via web services call to the CRM SDK.
The blog post from Microsoft support talks about auditing in more detail
Dynamics CRM Audit & User Access Data
I did work out an SQL query which gets last time a user logged onto CRM, The article above has a similar query
CRM 2011 – How to get the Last Login Date and Time of a CRM Instance
Creating a report for audit logs can be tricky because it has to be an SQL report because there are no auditing entities you can access.
Auditing doesn’t have a filtered view which makes a bit harder with regards to security/privileges. Filtered views are useful because they check the security/privildges the person running the report has, only showing the records they can see.
No filtered view means anyone viewing the audit report could be viewing records and fields they shouldn’t be able to see.
This does mean it’s not possible to create audit reports for CRM online.
I found a couple of examples of audit reports
DYNAMICS CRM 2011 AUDIT REPORT IN SSRS
Dynamic CRM 2011/2013 Audit Report in SSRS
CRM 2011 & CRM 2013–Usage Audit Report (On-Premise Deployment)
Audit CRM SDK Code examples
There are a couple of CRM SDK samples which show you how to get audit information programmatically.
Sample: Audit entity data changes
Recover your deleted CRM data and recreate them using CRM API
If you have to write an audit report for a CRM online, you could use the CRM SDK to get the data but it seems like hard work to me and probably time to tell the end user it’s not worth the effort.
It’s not a report as such, but the MSCRM Toolkit contains an “Audit Export Manager” that allows you to export your audit data to a csv/xml file. It uses the CRM SDK, so data security is applied and will work on CRM Online too.
I haven’t used the MSCRM toolkit before (not that I can remember). I will give it a test run, it could be a very useful tool for investigating live audit issues
The Audit Center does not work. Tried it today but keep getting an exception on export. Has to write a Data Service using the SDK to get the audit data. I’m on CRM 2016 8.1 Online.
And what about Audit Center in XrmToolBox 😛
This is great, suddenly I’m finding lots of tools to get at the audit data. Awesome
We are in process of upgrading from on-premise CRM 2011 / SQL Server 2008 Standard Edition to on-premise CRM 2015 / SQL Server 2014 Standard Edition. In either system we are unable to delete audit logs either via CRM application or the sample code provided in CRM SDK. In several articles, that I’ve read, mentions something about SQL Server standard edition not able to partition audit logs. However, what is not clear is that are we not able to remove the audit logs because we have SQL Server standard edition? Can logs be deleted directly from dbo.AuditBase table using Delete? any suggestions?
Hi!, using SDK I got all audit info into another database, with detail values. But I obtained a high percentage of data (95%) with the same values for old and new fields, i checked the same accesing audit summary on CRM. Is there a way to filter it using SDK, do you know why CRM captures auditing with same values for old and new fields?
You will receive audit record updates when plugins update groups of fields but don’t change the value.