Dynamics 365 audit explanation deep dive (2023)

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 beGDPR 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 changesto 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

(Video) Dynamics 365 Customer Insights Technical Deep Dive Part 1 - Data Ingestion and Unification

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 disablesthe 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 theSales, 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 theEntity Levelaudit, 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 generatea 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 likelast emailsent.

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 auditsthe 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 thebiggest table of the environment. The bigger the table, the longer it will take to query it.

(Video) Dynamics 365 Customer Engagement Deep Dive: Creating a Basic Plug-in

When auser wants to consult theAudit Historyfor a given record, he/she will open the related record from theAudit table. An HTTP request will be made to query the SQL database. If theAudittable 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 theDeleteAuditDataRequest class, it's possible to delete all audit data records up until a specified end date. For example, a simple batch could start for aStartDateand delete the audit day-by-day until anEndDate, as below:

// Iterate through each day to delete audittry{ 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 deletingthe 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 theAudit History Extractorplugin.

(Video) Microsoft Dynamics 365 for Customer Insights: Technical deep dive - THR3013

But the tool is far from perfect and may not be adapted for anintensiveandmassiveusage. 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 theAudittable 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!

TheAuditBasetable is composed of 16 columns and none of them is namedNew ValueorOld Value!

(Video) Onboarding and Inventory Deep Dive

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

CreatedOn
- When was the row created in the table.
- Corresponds to theChanged Datecolumn.
UserId
- Who changed the data. It contains the GUID from asystemuser record.
- Corresponds to theChanged Bycolumn.
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 theEventcolumn.
- To not be confused with the Operationcolumn, that contains only four values (Create, Update, Delete, Access).
ChangeData
- Corresponds to theOld Valuecolumn.
- It's a list where items are separated by thetildecharacter ~.
AttributeMask
- It's an comma-separated integer array, whereeach 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 theChangeDatacolumn.
- ```
SELECT ar.name,ar.ColumnNumber FROM MetadataSchema.Attribute ar INNER JOIN MetadataSchema.Entity en ON ar.EntityId = en.EntityIdWHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47
```
  - Where 2 corresponds to the Account entity and 47 to a given column number.
- Corresponds to theChanged Fieldcolumn.
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 theAudit Historyis 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 AuditBaseor 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!