Sonoma Partners Microsoft CRM and Salesforce Blog

Analyzing Audit Logs using KingswaySoft

If you have ever looked into analyzing audit log records in Dynamics CRM, you know how hard it can be.  Using the API there isn’t a good way to retrieve all the audit log records for a specific entity.  You can only either retrieve all the changes for a certain attribute or retrieve all the changes for a specific record.  If you’re on-premise and have access to the database, you can get to the audit detail records but you will find that the data is very hard to parse through.

Thanks to the wonderful folks at KingswaySoft, with version 7.0, this is no longer the case.  With KingswaySoft v7.0, audit details can easily be retrieved for a specific entity and then can be dumped into a file or a database for further reporting or analysis.

In order to accomplish this, first you will need to make sure you have the SSIS Toolkit installed and then download KingswaySoft v7.0 here.  Then open up Visual Studio and create a new Integration Services project.

clip_image002

Next add a Data Flow Task and drill into it.

clip_image004

Then we will set up a Dynamics CRM Connection using the Connection Manager.  In the Connection Manager view, right-click and select “New Connection”.

clip_image006

Now select the DynamicsCRM connection and click Add

clip_image008

This will pop open the Dynamics CRM Connection Manager which will allow you to connect to your Dynamics CRM organization.

clip_image010

Now use the SSIS Toolbox view to drag the Dynamics CRM Source component onto the canvas.

clip_image012

Double-click the Dynamics CRM Source component to pop open the editor.  Select the Connection Manager that you created earlier and set AuditLogs as the Source Type.  In the FetchXML text editor, write a fetch xml query to pull back the records of an entity where you want to retrieve audit details from.  In my example I’m retrieving 25 account records with my Fetch XML query.

image

Select Columns on the left and pick the columns you would like to be a part of your report.  In my example I’m going to use action (Create, Update, Delete, etc), the objectid and objecttypecode (the record that was changed), and the userid and useridname (the user that triggered the change).

clip_image016

The Dynamics CRM Source component will have two outputs, one for the header audit record and one for the list of audit detail records.  In my example I want to join these two outputs into one dataset so I can display both sets of data in the same report.  In order to do this we will need to drag two Sort components onto the canvas and then connect each output into the separate Sort components.  The result should look something like this:

clip_image018

Now double-click the first Sort to open the editor.  Select the auditid as the sort attribute as it is the unique key to join the two datasets together and check the “Pass Through” box for all the other columns that you want to use in your report.

clip_image020

Now double-click the other Sort component and perform the same steps.

clip_image022

Next drag the Merge Join component onto the canvas, connect the two outputs from the two Sort components into the new Merge Join component and then double-click the Merge Join component to open the editor.  Select Inner join as the Join type and then select any columns you want in your report and map them in the bottom pane.

clip_image024

Now we need to drag a Derived Column component onto the canvas and connect the output from the Merge Join into the Derived Column component.  This component needs to be used as we’re going to output the data into a CSV file so the oldvalue and newvalue columns need to be converted from a DT_NTEXT to a DT_TEXT.  Open the editor for the component and set the expression to convert ‘oldvalue’ to DT_TEXT using the 1252 codepage and repeat the same for ‘newvalue’.

image

Lastly, use a Flat File Destination to output the audit records into a CSV file that can be opened in Excel.  The screenshot below is the columns I used for my output file. 

image

Now your Data Flow should look like the following:

image

Then you can run the SSIS package and you should get an output file that displays all the audit records for the first 25 retrieved accounts.  The output will show the name of the user that made the change, the field that was changed, the old value, the new value as well as if it was a Create or Update.

image

So there you have it!  Thanks to the wonderful KingswaySoft toolkit, it is now possible to extract audit logs into a readable output that can be analyzed as needed.

Topics: Microsoft Dynamics CRM Microsoft Dynamics CRM 2015 Microsoft Dynamics CRM 2016 Microsoft Dynamics CRM Online