Thu, 01/14/2021 - 14:23 By Danny Rodrigue…
4 comments
Dynamics 365 audit explanation

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
Dynamics 365 audit explanation

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).
    • Dynamics 365 audit explanation

       

  • 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.
    • entity level

       

  • 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
audit log management

 

    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.

    Dynamics 365 audit explanation

    Well, it's not!

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

    audit base columns

    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

     

    Dynamics 365 audit explanation

    Comments

    You have referencesd he auditbase table for export but that is only applicable for on premise. You have no access to that table online and even using the new SSMS 18 TDS CDS read-only connection you still do not get access to audit when online. And storage is not really a problem if you are on-prem but can be massive problem online. Your only log storage can quickly exceed your actually data storage

    Sun, 02/07/2021 - 06:42
    Gary (not verified)

    Indeed, the Audit table is currently not accessible through the TDS endpoint. Stayed tuned for the part II of this article, we will explain why ;) #NiceStuffComing 

    And regarding the AuditTable for on-premise environments, indeed the storage is never the issue. But the issue arises when you want to retrieve the data - for example when displaying the Audit History. As already experienced in too many projects, you can get a SQL timeout when the table becomes out of control.

    Fri, 02/12/2021 - 10:16

    In reply to by Gary (not verified)

    So yes, you can access it, filter it, ... as long as you manage paging cookies, you can get it all provided you have a lot of patience.

    Eager to find out where python can provided an advantage.

    Mon, 10/04/2021 - 16:09
    Christopher Wi… (not verified)

    In reply to by Gary (not verified)

    Hi Christopher,

    Yes we can! Web API is very often the right way to go.

    But in this specific case where there is a lot of data audited, we've experience many many timeouts when calling the web API. It wasn't able to ever be used in production.

    The decision to use Python and Pandas here is to gain time during the development. Pandas is - in my opinion - the best way when dealing with some amount of data. And I found it quite easy to read and understand :)

    Tue, 10/05/2021 - 11:23

    In reply to by Christopher Wi… (not verified)

    Add new comment

    Image CAPTCHA
    Enter the characters shown in the image.