Please note that this blog post has been updated to reflect the latest changes in our AuditLogs output from KingswaySoft Dynamics 365 CE/CRM Source component.
Microsoft Dynamics’ Auditing feature has many benefits, including help analyze the history of a particular entity record and get a summary of the changes that have happened. For a business, this is useful to help improve processes. Audit logs help identify changes to entity records and security roles, operations that happen on a record, and other events. This information can be useful to developers and administrators alike. Auditing is possible only if Enabled from the CRM side, and you can do that by navigating to “Settings” and then to “Global Audit Settings” and finally, “Entity and Field Audit Settings”.
KingswaySoft's SSIS Integration Toolkit for Dynamics 365 offers a robust and easy way to retrieve and read audit logs. There are two methods to completing the task.
Method 1: Source Entity as Audit
The first method involves choosing the Source Entity as Audit from our CRM Source Component. Source Entity as Audit will give the output audit records for all entity records with the respective actions performed and their corresponding details. You can filter through them by entity names.
Method 2: Source Type as Audit Logs
The second method, the more detail-oriented method, is to choose the Source Type as Audit Logs in the CRM Source component, and it requires a FetchXML query.
To retrieve the audit logs for a single entity, all you have to do is write the below query:
<fetch mapping="logical" version="1.0">
<entity name="account">
<attribute name="accountid" />
</entity>
</fetch>
The above example is a simple query that reads audit records for the account entity. The query could be modified to write more complex queries that can be handled by our component. For example, you can add filtering conditions to get the audit logs for the records created over the last two days by writing the query as below:
<fetch mapping="logical" version="1.0">
<entity name="account">
<attribute name="accountid" />
<filter>
<condition attribute="createdon" operator="last-x-days" value="2" />
</filter>
</entity>
</fetch>
Please note that this will filter for the createdon field with respect to the entity (i.e.: when the entity records were created) and not on audit log records.
Here is an example of a design to connect all the related outputs to database tables using our Premium ADO.NET Destination component, which is a part of our SSIS Productivity Pack.
The audit log outputs are as below:
- Primary Output contains the entity-level audit information.
- Audit Details (Attribute Changes) Output contains the audit details, mainly field-level changes, including the field's old values and new values.
- Audit Details (Relationship Changes) Output contains the audited details of a change in a relationship.
- Audit Details (Record Sharing) Output contains the details for record sharing.
- Audit Details (Role Privileges) Output contains the changes to the privilege of a security role.
The five outputs written to five different tables can then be joined through auditId, which is a common key.t
Now, this would work if you are trying to get the audit data from only one entity. In case you need to get it for all entities, or maybe for a group of entities, then a slight add-on would have to be provided to the package design.
A Foreach Loop Container can be used to iterate through each entity name, and thus get the audit logs for all the entities. The design could be as below:
The first data flow task will write the entity names to a variable, and the Foreach Loop Container can be used to get the variable values into the CRM Source component. The FetchXML section can use variables directly, and the below query can be used:
"<fetch mapping=\"logical\" version=\"1.0\"> <entity name=\""+lower(@[User::Variable])+"\"> <attribute name=\""+lower(@[User::Variable])+"id\" /> </entity> </fetch>"
The variable, which will have the entity name (assigned by the first data flow task), could be concatenated with “id” in order to get the primary field. For instance, if “opportunity” is the entity name, then “opportunityid” will be the unique identifier. For “lead”, it would be “leadid”. When you run this package, the output will be logs files generated that contains the audit log details, along with the unique identifier to distinguish them.
An exception for this would be activity-associated entities (Phone Call, Email, Fax, etc.), in which case, they will have activityid as the unique identifier.
Apart from the above, another method to extract audit records would be to write a FetchXML query on the audit entity to get the outputs. In this way, you can add complex conditions to get filtered outputs. For example, if you would like to fetch the records for all the entities updated in the last two days, you could write the query as below:
<fetch version="1.0" >
<entity name="audit" >
<all-attributes/>
<filter>
<condition attribute="action" operator="eq" value="2"/>
<condition attribute="createdon" operator="last-x-days" value="2" />
</filter>
</entity>
</fetch>
For a list of properties for the Audit EntryType, you can to refer the reference document: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/audit?view=dynamics-ce-odata-9
This way, you get filtered audit logs for CRM entity records.
Final Note
Auditing is critical in helping you understand your business. It not only identifies the source and cause of errors and thus troubleshoot user issues, but can also help track unauthorized or fraudulent changes in data. With the ability to use our tools to specify the variables you would like to focus on, you can hone in your monitoring activities to create the data tracking you require.