Saving space & Extracting Audit Logs in Microsoft Dynamics 365

Audit logs can take a lot of space on CRM instance, and data storage costs money according to Microsoft subscriptions. To avoid this bother - set auditing to turn on for necessary fields only. You can also clean old audits until the established date or just for some predefined entity.

Yet, if the history of changes in your system is essential, there is no option to delete or turn off anything. In that case, you can export audits to your computer or server and save space by deleting audits on your online Microsoft Dynamics instance. In this article, you will see all necessary steps for it. Do you want to have your audits in CSV format? Or maybe it is better for you to export them to your SQL database? Both options are described in the instructions below. I will show you how to do that for Account entity. But you can do the same for any entity.

Instructions:

Export to CSV

0)    Download and install SSIS Integration Toolkit for Microsoft Dynamics 365 from https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download

1)    Start Visual Studio and create anew Integration Services Project.

2)    Add a new DynamicsCrm connection.

3)    Select appropriate service endpoint, authentication type, enter CRM discovery server*, provide user credentials and select target organization from Organization option set. Then test the connection.

*To get CRM discovery server URL, go to CRM, navigate to Settings -> Customizations -> Developer Resources and copy Discovery Service Endpoint Address.

4)    Once the connection is successfully tested navigate to Data Flow tab and add a new Data Flow task.

5)    Drag Dynamics CRM Source element from SSIS Toolbox to the Data Flow Task and configure it.

6)    Select the Connection Manager created on step (2-3), select the Source Type as AuditLogs.

Then use FetchXML* to specify the records that you want to pull data for by adding with the entity and the primary key of that entity. Refresh CRM Metadata and click OK.

*To obtain Fetch XML filter desired records with CRM Advanced Find, click “Download Fetch XML” and remove all attributes except of entity ID field.

7)    Add two sort elements to the Data Flow Task.

8)    Connect first Sort element to CRM Source Primary Output, second Sort element – to Attribute Changes output (add more Sort elements to obtain other Audit details.

9)    Specify auditid column for each filter

10) Merge output data from Sort elements with Merge Join element. Specify desired properties.

11)  Add a Derived Column component and connect the output from the Merge Join to it. This component is used to convert a DT_NTEXT type values to a DT_TEXT type to be able to write data to CSV file.

Select desired columns, give them appropriate names and add “(DT_TEXT,1252)” before column expression for each column of type DT_NTEXT.

12) To export obtained audit data to CSV file add Flat File Destination element. Create a new Flat File Connection manager with a Delimited file format.

Create an empty CSV file to export audit data to and enter the path to it.

Navigate to Advanced tab and remove Merge columns

Check mappings in Flat File Destination Editor

13) Just build the solution and execute the package

You can track execution progress

Once the package execution is completed, check the output CSV file.

Export to SQL database

After step (6) right-click on Connection Managers and select New Connection. In the dialog select OLEDB.

In the next dialog select New…

Let’s export data to the local DB.

For Server name, enter “localhost” and select the existing database name.

Verify that “Use Windows Authentication” option is selected.

Select “Test Connection” to verify that the connection settings you have specified are valid.

*When localhost is specified, the connection manager connects to the default instance of SQL Server on the local computer. To use a remote instance of SQL Server, replace localhost with the name of the server to which you want to connect.

Add two OLE DB Destination elements (for Primary output and Attribute changes).

Create a new database table for each of the destinations and check the mappings.

Build solution and execute the package.

Check the tables in the database to verify the audit data have been exported properly.

The final stage is to check your exported audits, make sure that you have all necessary records in the bounds of your timeframe.

Settings -> Auditing -> Audit Log Management -> Then select Logs that you want to delete and press the corresponding button.

Summary:

In this article, we have shown you how to extract audits and have it on your computer or server. That is easy if you have already worked with SSIS Integration Toolkit. You need to build correctly your Data Flow, download appropriate FetchXML, create two Sort elements and then merge them.

We hope that helped! Please let us know if you have any questions.

Slava Kostiuk and Roman Savchenko, UDS Systems https://uds.systems/