Dynamics 365 audit explanation deep dive

Dynamics 365 audit explanation:

Audit in Dynamics 365 - Part I

Why

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.

What

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

When

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.

How

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.

Where

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!

Delete Audit

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 above method will delete all audit history of all entities within the time frame selected. There is not the ability to delete the audit history of only certain entities within a certain time frame through the out-of-the-box methods.

Additionally, the SDK also offers the possibility to clean the audit base. With the DeleteAuditDataRequest class, it's possible to delete all audit data records up until a specified end date. For example, a simple batch could start for a StartDate and delete the audit day-by-day until an EndDate, as below:

// Iterate through each day to delete audit
try
{
    foreach (DateTime day in EachDay(StartDate, EndDate))
    {
        LogHelper.LogInformation($"Process Day {day.ToString(DATE_FORMAT)} ...");

        try
        {
            // Create a delete request with an end date earlier than possible.
            var deleteRequest = new DeleteAuditDataRequest{
                EndDate = day
            };

            // Delete the audit records.
            m_orgService.Execute(deleteRequest);
        }
        catch (Exception ex)
        {
            LogHelper.LogException($"Error while deleting audit for date {day.ToString(DATE_FORMAT)}", ex);
        }
    }

}
catch (Exception ex)
{
    LogHelper.LogException("An error occured in the 'DeleteAuditFromDate' batch : ", ex);
}

Export Audit

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.

Dynamics 365 audit explanation on structure

One might think that the data inside the Audit table is stored as it is presented to the user. Meaning, that it's a simple SQL table with more or less six columns, in string or date format.

Well, it's not!

The AuditBase table is composed of 16 columns and none of them is named New Value or Old Value!

Among all this columns, six of them are to be highlighted:

• CreatedOn
  • When was the row created in the table.
  • Corresponds to the Changed Date column.
• UserId
  • Who changed the data. It contains the GUID from a systemuser record.
  • Corresponds to the Changed By column.
• Action
  • It's an integer representing what kind of action has been performed on the record (e.g: Create, Update, Deactivate, Add Member,  ...). 

  • SELECT Value as Action, AttributeValue as ActionValue 
    FROM StringMap 
    WHERE AttributeName='action'

     

  • Corresponds to the Event column.

  • To not be confused with the Operation column, that contains only four values (Create, Update, Delete, Access).

• ChangeData
  • Corresponds to the Old Value column.
  • It's a list where items are separated by the tilde character ~. 
• AttributeMask
  • It's an comma-separated integer array, where each number can be mapped to a single column.
  • Each integer correspond to the ColumnNumber metadata property of attributes that have been updated.
  • The items are in the same order as the items stored in the ChangeData column.

  • SELECT ar.name,ar.ColumnNumber 
    FROM MetadataSchema.Attribute ar INNER JOIN 
      MetadataSchema.Entity en ON ar.EntityId = en.EntityId
    WHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47

    • Where 2 corresponds to the Account entity and 47 to a given column number.
  • Corresponds to the Changed Field column.
• ObjectTypeCode
  • The entity of the record that is concerned about the action.

  • SELECT ObjectTypeCode, LogicalName 
    FROM Entity 
    WHERE ObjectTypeCode=1

     

• ObjectId
  • The GUID of the record that is concerned about the action.
  • Corresponds to the current record in which the Audit History is displayed.

From that information, we can have a pretty good picture of how Dataverse deals with its audit:

• As you can see, only old values are stored in the table! Therefore, to display the new value, another request must be made, either in the AuditBase or in the current CRM data.
• One row in the AuditBase table can contain multiple changes in different fields. 
• The structure of the AuditBase table is very generic, with mainly integers pointing out to other reference tables. This explains why everything can and goes into this table!

Coming Next

In the second part of this article we will see how we can play and manipulate the AuditBase table. Using some Python code, we will turn this data into something useful for any Data Management policy!

Links

• https://docs.microsoft.com/en-us/power-platform/admin/audit-data-user-activity
• https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/developer/entities/audit#read-only-attributes
• https://mahadeomatre.blogspot.com/2015/02/ms-crm-audit-database-table-details.html
• https://www.ahaapps.com/microsoft-dynamics-365-audit-management/
• https://www.qgate.co.uk/knowledge/managing-audit-logs-in-microsoft-dynamics-crm/

Dynamics 365 audit explanation