Dynamics CRM Audit History Download

16 July 2014

Dynamics CRM 2013 and Dynamics CRM Online both have excellent auditing capabilities. Unfortunately, neither platform allows the extract of the full audit trail in bulk but require explicit item by item extraction. There are several methods of extracting the details directly out of the on-premise Dynamics CRM SQL Server but I would always shy away from these solutions as they are unsupported by Microsoft.

The solution I am going to present requires 3 stages

  1. Extraction of the core 'Audit' entity into a SQL Server table.
  2. For each Audit entity the extraction of the detailed audit record into a 'Dynamic Column'.
  3. Filtering out subsequent requests so that we do not repeatedly extract the same audit detail unnecessarily.

Step 1: Extraction of the core 'Audit' entity into a SQL Server table.

Open the Connect To Data Source dialog and complete the details for you Dynamics CRM Online instance.

Connect

Once you have connected DS3 will display all of the available attributes in the Data Source (A) tree.

Data Source A

We are going to add a new Dynamic Column called auditdetails to the source giving our complete source schema.

Step 2: For each Audit entity the extraction of the detailed audit record into a 'Dynamic Column'.

To add a Dynamic Column you first need to enable the Dynamic Column feature. From the View menu select the 'Dynamic Columns Window'. This will present you with the dynamic Columns tab. On this table click on the 'Enable Dynamic Columns' Link.

Dynamic Columns

Dynamic Columns are a very special feature which allows users to create new columns from code. The code can re-use the source and target connections as well as the values of the current row being connected to. When you enable the feature you get a default code template.

Dynamic Columns

If you are familiar with c# code then this will be no surprise. We are going to use some SDK code to extract the audit details for each row but only if their has been a change.

Firstly we are going to add some references to the necessary Dynamic CRM SDK's:

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;

using System.Runtime.Serialization;
using System.Xml;
using System.IO;

// These namespaces are found in the Microsoft.Crm.Sdk.Proxy.dll assembly
// located in the SDK\bin folder of the SDK download.
using Microsoft.Crm.Sdk.Messages;

In between the BeginRow() and Setup() methods we are going to add the following code.

The code essentially exposes the Audit change details as a property called auditdetails which returns into an xml formatted string which we can store in the database.

public string auditdetails { get { 
    
        
        //Only get the audit details if there is a create,  update or delete
        if(actionname =="Update" | actionname =="Create"| actionname =="Delete")
        {
            Trace.WriteLine("Row {0}",NEXTINT());
            
            //Use the current connection
            Simego.DataSync.Providers.MSCrm2011.MSCrmDataSourceReader reader = (Simego.DataSync.Providers.MSCrm2011.MSCrmDataSourceReader) this.DataSourceA.Reader;
            OrganizationServiceProxy orgService = reader.Connection.GetProxy();
            //Create a history request
            RetrieveRecordChangeHistoryRequest changeRequest = new RetrieveRecordChangeHistoryRequest();
            changeRequest.Target = new EntityReference(this.objecttypecode, this.objectid);

            //Get the history request from the CRM system
            RetrieveRecordChangeHistoryResponse changeResponse = (RetrieveRecordChangeHistoryResponse)orgService.Execute(changeRequest);

            //Serialise the response into an xml string
            using (var stringWriter = new StringWriter()) 
            {
                XmlWriterSettings settings = new XmlWriterSettings { Indent = true };
                using (var writer = XmlWriter.Create(stringWriter,settings)) 
                {
                    var contractSerializer = new DataContractSerializer(typeof(RetrieveRecordChangeHistoryResponse), new List<Type> (new [] { typeof(AuditDetailCollection) } ));
                    contractSerializer.WriteObject(writer,changeResponse);
                }
                return stringWriter.ToString();
            }
        }
        //return nothing if it is not create,  update or delete
        return"";    
    } }

This result is essentially the exact contents of a 'RetrieveRecordChangeHistoryResponse' as defined by the Dynamics CRM SDK. Why did we do this? We can then at a later date extract the full details of the change by loading it back into a RetrieveRecordChangeHistoryResponse (using serialize and deserialize).

Pressing the Build button converts the auditdetails code into a column

Dynamic Columns

We can now use another of DS3's unique features which will create a SQL table exactly the same schema as the Data Source (A). From the tools menu select Tools -> Create SQL Table. You will be prompted through a wizard, which will eventually create a SQL Table in a SQL Server and connect Data Source (B) to it.

SQL Wizard

Once connected the SQL Server Table, the schemas should automatically map themselves together.

Mapping

We could now read all of the details from the audit entity and write them to the SQL Table. If we consider the probable size of the audit table this may be an extremely long process to repeat unnecessarily. So we need to implement some way of telling DS3 that we have already written the audit record to the local SQL Server so there is no point in calling back to Dynamics CRM again. We can do this by using a lookup into the target SQL Audit Database.

Step 3: Filtering out subsequent requests so that we do not repeatedly extract the same audit detail unnecessarily.

For each row we need to lookup into the Data Source(B) whether we have already extracted the audit record. We can achieve this by creating another 'Calculated Column' called 'AuditValueInDatabase' and using the LOOKUPB function. Essentially, this tells DS3 to find the auditid value from the CRMAudit table where the audit id in CRM matches the Auditid in the database. If the result is has no value then we can skip extracting the record.

Calculated Column

The next step is to now filter the results to only include items we cannot find in the target SQL Table. This is easy as DS3 has a filter feature which we can set to when 'AuditValueInDatabase' is null (nothing returned).

ISNULL(AuditValueInDatabase)

Filter

We are almost finished our solution. Ideally we need to keep extracting the audit trail to keep it up to date rather than extracting the whole audit table every day. We will use the 'FetchXMLFilterExpression' feature to extract only the last 2 days audit entries. We can run this extract every day and in the case that we do not run it for a day it will catch up without writing duplicates as DS3 will reconcile the changes first before writing any records. The fetchxml looks like:

<filter type="and">
     <condition attribute="createdon" operator="last-x-days" value="2" />
</filter>

We can now run our complete Dynamics CRM Audit download project every day.

| |