Here is a great article about the audit table inside the Dataverse Dynamics 365 audit explanation deep dive | Dynamics Chronicles (dynamics-chronicles.com)
Information below is copied from the article for preservation. I can delete upon request.
From user access to a model-driven app to a field being updated, it is nowadays mandatory for a customer platform to be able to explain why a process has been launched and what has occurred with client-related data. As the good CRM that it is, Microsoft Dynamics 365 (or Customer Data Service, or Dataverse, or D365, or “the CRM powered by Microsoft”) can monitor and keep track of all changes made to its data.
The Microsoft Dynamics 365 / Dynamics CRM auditing feature logs changes that are made to customer records so that details can be reviewed at any time. The auditing feature is designed to meet the auditing, compliance, security, and governance policies of many regulated enterprises – something mandatory to be GDPR compliant.
If you are in charge of data management, the audit history must be one of the tools you master from A to Z.
Audit history is used to track changes to any records. The audit logs can answer questions such as:
- Which user was accessing the system, and when?
- Who updated this field value on this record, and when?
- What was the previous field value before it was updated?
- What actions has this user taken recently?
- Who deleted this record?
An audit entry will provide information about:
- When did the action occur
- Who performed the change
- The type of change
- The regarding object (e.g: field name)
- The previous value
- The new value
The following operations can be audited:
- Create, update, deactivate, and delete operations on records.
- Changes to the sharing privileges of a record.
- The N:N association or disassociation of records.
- Changes to security roles.
- Audit changes at the entity, attribute, and organization level. For example, enabling audit on an entity.
- Deletion of audit logs.
Auditing can be enabled at 3 levels and are either disabled or enabled by default.
- Organization Level: Enables or disables the audit logging for the entire organization. Administrators can find it under Settings > Auditing > Global Audit Settings or under System Settings > Audit tab. The audit can also be enabled for a specific area (e.g: disabled for the Sales, but enabled for Customer Service).
- Entity Level: Enables or disables the audit history for all records of a given entity. As of January 2021, this is still administered via the old interface.
- Field Level: Same as for the Entity Level audit, but at the field level.
Audit history is important when dealing with important fields in important entities. When Dynamics 365 audits an entity or a field, it might generate a lot of data. That is why enabling auditing for all entities and all fields should never be done!
It is the best practice to disable auditing in the extraneous entities and fields and enable auditing only for the entities and fields which need to be truly audited, for example address fields changes or client last name changes, but not for a rollup field like last email sent.
With Auditing enabled, logs can significantly increase the size of the database and may hurt the entire system. If you are in charge of data management, knowing how many audits the system had and when and how to purge it is essential.
Additionally, you may want to stop auditing for maintenance purposes. Stopping auditing stops tracking for the organization during the period until auditing is started again. When you start auditing again, the same auditing selection is maintained that was previously used. The same idea applies when doing a migration.
Audit History Data Deluge
Too many Data
When speaking with business users, it is not rare to hear phrases like “all fields are important and should be audited“. That’s where the technical people of the project must intervene! Having too many entities and fields audited will affect the performance of the system. Behind the scenes, there is an SQL table that will store all audit history entries. And in many projects this table will turn out to be the biggest table of the environment. The bigger the table, the longer it will take to query it.
When a user wants to consult the Audit History for a given record, he/she will open the related record from the Audit table. An HTTP request will be made to query the SQL database. If the Audit table is too big, it might lead to a query requiring more than 2 minutes to perform. In such cases, the user will see an SQL timeout error and never be able to consult the audit history. Just imagine the frustration of your sales manager when – after waiting for two full minutes – a technical error appears on the screen!
Therefore, it is good practice to delete the logs which are no longer needed. However, the decision to delete old audit history needs to be consulted with all stakeholders. Some stakeholders might not be comfortable with the deletion of old audit history. In some industries, it might even be illegal to delete the audit for certain field!
If the decision has been made to delete the audit history, do the following:
- Go to Settings > Auditing
- Click on Audit Log Management
- Click on Delete Logs
The downside of deleting the audit is… well it’s deleted and not archived! So before deleting audit entries, it would be great to first export them! Currently, the is only a single way to do so. As always, it’s via the XrmToolBox and one of its plugins, in this case the Audit History Extractor plugin.
But the tool is far from perfect and may not be adapted for an intensive and massive usage. Below I will demonstrate another way to extract the audit information and not lose it forever when purging the table.