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.
Next add a Data Flow Task and drill into it.
Then we will set up a Dynamics CRM Connection using the Connection Manager. In the Connection Manager view, right-click and select “New Connection”.
Now select the DynamicsCRM connection and click Add
This will pop open the Dynamics CRM Connection Manager which will allow you to connect to your Dynamics CRM organization.
Now use the SSIS Toolbox view to drag the Dynamics CRM Source component onto the canvas.
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.
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).
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:
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.
Now double-click the other Sort component and perform the same steps.
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.
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’.
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.
Now your Data Flow should look like the following:
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.
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.