How to export your Dataverse audit logs to a Data Lake

Dataverse has great out of the box auditing capabilities. Configuring the tables and columns you wish to audit is simple. Users have the ability to access the audit history for a record directly from its form. Audit logs consume log storage capacity which is expensive and you can configure the retention period for your audit logs to reduce your log storage usage. Synapse Link for Dataverse now supports exporting your audit logs to a Data Lake. You can use this to store your audit logs indefinitely at a much lower cost than Dataverse log storage. You can also easily query your audit logs table in your Data Lake using tools like Power BI or TSQL

You need to be configure your Synapse Link to export in Delta Lake format to include the audit table in the export. To use Delta Lake you need to have an Spark Pool provisioned and configure this when you create your Synapse Link for Dataverse. Spark Pools do incur costs when running so you need to aware of these additional costs if you are not already exporting in Delta Lake format.

Create a Spark Pool for Synapse Link for Dataverse

Your Spark pool needs to be version 3.3 and at least medium node size to use it with Synapse Link for Dataverse.

Spart Pool version 3.3 for Synapse Link for Dataverse

Once your audit log gets synched to your data lake you can query the audit table in your data lake.

sql query audit dataverse audit table in data lake

There are a few columns in the audit table you worth looking at

changedata – contains the details of the changed data in Json format
objecttypecode – the type of object the audit record relates to e.g account
action – type of action create, delete, assign
objectid – the guid of the record being updated

For example I can query the audit tables for all the audit records relating to the ABC Group account by using its guid

SQL query azure data lake for audit table for account using its guid

The changedata column contains details on what was changed in Json format. Sample below

{"changedAttributes":[{"logicalName":"name","oldValue":"ABC","newValue":"ABC Group"},{"logicalName":"creditlimit","oldValue":null,"newValue":"55000"},{"logicalName":"creditlimit_base","oldValue":null,"newValue":"55000"},{"logicalName":"websiteurl","oldValue":null,"newValue":"https://www.abcgroup.com"},{"logicalName":"telephone1","oldValue":"5555555","newValue":"01 234 1456"},{"logicalName":"paymenttermscode","oldValue":null,"newValue":"1"},{"logicalName":"industrycode","oldValue":null,"newValue":"3"},{"logicalName":"address1_shippingmethodcode","oldValue":null,"newValue":"2"}]}

Exporting your audit logs to a Data Lake, in combination with a reasonable audit retention policy in Dataverse, should allow you satisfy your organizations data retention policy without costing a fortune. I have written some other posts on Synapse Link for Dataverse which may be of interest.

Audit Logs – Synapse Link for Dataverse