When auditing is enabled in a CRM/CE/Dataverse entity, it stores the audit details in your instance, i.e., any historical change-related details in the form of audit logs. When this is left unhandled, it can consume space in your instance, and therefore, you might be required to periodically archive audit logs by having them saved to database tables (or any other storage). Once archived, these audit logs may be deleted from the CE/CRM instance, if your business users do not rely on them. This can be done as a manual process from your CRM/CE instance, however, using KingswaySoft, the process can be performed as an automated data flow in SSIS. In this demonstration, we will be looking at how to achieve this. We will be using the below components:
- Dynamics 365 CRM/CDS Source component
- Dynamics 365 CRM/CDS Destination component
- Premium ADO.NET Destination component
- Premium Derived Column (if required, for any transformations)
- Data Spawner
The above components can be downloaded as a part of our SSIS Integration Toolkit for Microsoft Dynamics 365 and SSIS Productivity Pack.
Archiving Audit Logs
In order to read and save audit logs, we select the Audit Logs source type in KingswaySoft Dynamics 365 CRM/CDS Source component, as shown below.
We have a detailed blog post showing how to work with the "Audit Logs" type in the link below:
Now, to save them to a database table, you can use a Premium ADO.NET Destination component. Here you will notice that you have five outputs associated with audits. You can save these into separate database tables, perform a Union All, or a Merge join to save them into one database table.
This would be your archival process, where the audit logs are stored for future reference or reporting purposes. After this, comes the process of deletion of those outdated audit log records from the CRM instance, which we will explain in the next section.
Deleting Audit Logs
You could drag and drop a Dynamics 365 CRM Destination component, choose a connection manager, and select the Delete action. Then, search and choose the audit entity as shown below.
On the Columns page (metadata), you can see that three fields have been discovered:
- enddate
- targetid
- targettype
You have two ways to delete from the audit entity. Either delete all audit recordsuntil a certain date or delete all audit records for a particular record in an entity. Let's see how these methods can work out.
Case 1: Delete historical audit logs by the enddate
If you choose to delete the audits until a certain enddate, then,
-
- You would start by using a Data Spawner component, which would generate one row to feed your SSIS pipeline. In the Data Spawner component, you can create a column that would hold a date value, which is the cut-off date for the audit log truncation.
- As an alternative to using a Data Spawner component to provide the date value, you could also have the value defined in a Premium Derived Column component. Note that both components support using variables as column values should you need to make the process dynamic.
- As mentioned above, you would then create a Dynamics CRM/CDS destination component that performs a Delete action on the audit entity. The component should be connected to the above Data Spawner or Premium Derived Column component. In the destination component's Columns mapping page, you would simply map the above-mentioned date value column (from either the Data Spawner or Premium Derived Column component) to the enddate field in the destination component.
- When executed, the data flow should delete all the audit records up until the date specified.
- While performing this method, the targetid and targettype fields would need to be unmapped.
- You would start by using a Data Spawner component, which would generate one row to feed your SSIS pipeline. In the Data Spawner component, you can create a column that would hold a date value, which is the cut-off date for the audit log truncation.
Case 2: Delete by targetid
If you chose to delete the audits for certain records, then,
-
- Your input can be a list of records instead of one record, as we have explained above using the enddate option.
- In the columns mappings page, you would need to map the GUID value of the record to the targetid field and the record type to the targetidtype. This GUID can be obtained using a CDS/CRM Source component or a previously stored record value from any source type.
- For example, if you want to delete all the audit records for a specific contact, you would map the contactid (GUID) to targetid and map the input that holds the value contact (as a string) or its entity type code (which is 2 for contact) to targetidtype.
- While performing this method, the enddate would need to be unmapped.
If you do not have the GUID available, you could possibly leverage the Text Lookup feature available in our CDS/CRM destination component. See the Online Help Manual linked below to learn how to configure and use the text lookup options.
Conclusion
The above can be provided as a chained data flow process by using two different data flow tasks - one for archiving and the other for deleting audit logs - in a timely manner. And as discussed before, the date values can be provided as a variable in our Premium Derived Column component, to have the package pick specific dates in each run. Please do note that the delete action for the audit entity requires an input for either the 'targetid' or 'enddate' field, but not both.
We hope this has helped!