Managing the Audit Table in Dynamics 365

All about the Dynamics 365 On-Premises Auditing: Dealing With Audit Table Size

The Dynamics 365 field auditing can now be written to the table of the AuditBase in MSSQL. This is because of the configuration of the auditing in the system.

The AuditBase Table for Dynamics 365

The AuditBase table is managed in partitions with the help of the SQL Enterprise. The system creates a separate audit partition every quarter. The Dynamic 365 now can functionally delete the quarterly audit partitions and obviously, the oldest can be deleted first. This is only possible because of the security privileges available at hand. The record level audit data sometimes get deleted for a relevant time period. The availability of the audit data will no longer be present in the Dynamics 365.

The AuditBase is known to have no constraints. Therefore it would violate the cause by moving the data from the table. The same collation as the Dynamics 365 production database can be set by copying the audit partition’s records in the form of the audit archive database.

Dynamics 365 Audit records will contain information of the GUID of the record that was updated or edited. So if a phone number was updated, the Audit trail will have the GUID of the phone. Obviously, the Audit record will have its own GUID.

Support to any kind of query-based access or reporting for say can be resolved by joining the archive database and the production database together. From time to time as and when audit partition deletion takes place the audit data is regularly achieved. This happens even before the audit deletion takes place. In this way by joining the audit and the production databases regular reporting takes place.

A Detailed Insight into Auditing Needs and Purposes

Auditing is done and carried out in order to meet a lot of purposes and requirements. It essentially helps with the compliance, to meet the requirements of the government and for security purposes. Also, one should rightly note that with the help of Dynamics 365 one can easily capture the following:

● It can address the time when any user in accessing a record and the revisions, changes, and rearrangements that are made.

● The operational details can be easily created, updated, deactivated and also completely deleted with its help.

● It also enables us to compare the original attribute value with the new attribute value.

● One can also easily change to privileges like the sharing.

● There can be changes made in the record privileges.

There is an appropriate type of security setting available. Hence one can easily access the system auditing configuration under any kind of Auditing in the Settings section of Dynamics 365. In the Customization Section, the Entity and attribute are configured accordingly. Creation and addition of the custom fields and entities are done in this part of the system.

How to create Audit Archive Database in the MSSQL Enterprise?

This can be done by making the use of MSSQL Management Studio. One can create a new dedicated Database for this purpose. In this kind of database, the Dynamics 365 can audit the records prior to the audit partitions in the Dynamics 365. One needs to keep in mind that the newly created audit record in the archive database will use the same collation as the D365 Production database. Here the source will be originally archived from the audit records.

What does One mean by Collation?

It refers to using a new set of rules. these rules will guide and determine the way the data will be sorted and compared. These rules are made use of to sort out the character sequence. There are options present which is specific to case sensitivity, kana character types, accent marks and the definitely the width of the character. In this way, the data consistency will be ensured.

At this point in time, the DBA is in the best position to duplicate the schema.

Purging Pre-requisites: Archiving the Audit Records

The audit records should be archived prior to purge. The audit partition will be created quarterly, but in some cases, it might not take place necessarily. There must be some yearly or biannual requirement of some archived audit data. We can conclude this because of the analysis of the numerous amount of audit data. Therefore the two years of audit data should be maintained in Dynamics 365. the running of annual data archive or the popular purge procedure, can effectively solve the problem.

There are numerous methods available to copy a Dynamics 365 audit partition’s records. The database administrator can effectively identify the preferred organizational database.

Two distinct approaches which could be deployed are the MS SQL Management Studio menu commands and the SQL scripts. One can also consult and properly create scripts with assistance from the infrastructure team. This will effectively ensure the integrity of the data.

Joining the Audit Archive and the Dynamics 365 Production

One of the most essential factors is how to maintain the continuity and integrity of the data between the Dynamics 365 production records and the different archived audit records. This can be done by joining them together. There is a unique identifier for each record and this is known as the GUID. The full form of the GUID is Globally Unique Identifier. The GUID can be sometimes thought as the primary key when the record (say contact details) is in relation to some secondary record (the case).

The primary key might be pointing to multiple secondary keys creating a one to many (1-to-N) relationship. One contact tagged with multiple cases. Say in the case of group insurance covers, one person (with contact details) representing multiple family members (cases) covered under the policy.

A join can be performed between the Dynamics 365 audit record having its GUID and the actual record (Phone number in this case), on which the update was performed. Third party reporting tools can be used to fetch elaborate reports on the archived audit records related to a certain Dynamics 365 specific record (Phone number in this case).

The Database Administrator (DBA) can use such reporting tools to deduce extensive Data Warehousing strategies, crucial for predictive and adaptive analysis.

The purging of the Dynamics 365 Audit Partitions

After the successful validation of the Dynamic 365 production audit Data, we come to the final step. the final step is to make any kind of deletion of the archived audit partition or partitions.

With the help of the security facilities, Dynamics administrator can go around the Auditing section mentioned in the Settings area. Here the Audit Log Management is also an option available.

Deleting a partition removes the record level audit data for the related time period. Audited details for the time period represented by the audit partition will no longer be available in Dynamics 365.

Finally, we can conclude that the Dynamics 365 will help to create a unique audit log partition for each and every quarter. However, the oldest partition will be the first one to be deleted. You can compare the deletion of the partition by clicking on the ‘DELETE LOGS’ option.