Audit Entity / Table – Few key points (Dynamics 365 / Power Apps)

In the earlier post we looked at different ways of extracting Audit History data.

Extract Audit History –https://nishantrana.me/2021/05/17/how-to-export-the-audit-history-values-from-dynamics-365/

We can use Microsoft 365 Security and Compliance Center for the same, though it is limited to the Production environment only.

https://docs.microsoft.com/en-us/power-platform/admin/enable-use-comprehensive-auditing#requirements

Then we have Audit History Extractor, and we can also write SSIS Packages as well as Custom Code.

Let us just revisit some of the key points with regards to the Audit entity.

• Do we have the Audit entity available for Advanced Find? >> No.

• Do we have it available inside Report Wizard? >> No.

• Can we write SSRS Report against the Audit entity using the TDS endpoint?

Let us create the Data Source.

Select the authentication as Active Directory Password Authentication for the TDS endpoint.

Enter the database name manually.

The final connection string >>

Data Source=orgnamae.crm.dynamics.com;Initial Catalog=orgname.crm.dynamics.com;Encrypt=True;TrustServerCertificate=False;Authentication=”Active Directory Password”

Within SQL4CDS the following query works

However, the same query doesn’t work inside SSRS.

It will give the below error message >>

“Table audit is not available for reports“

• Do we have the Audit entity in the Power BI Dataverse connector? >> No

However, we can use the OData endpoint to create the report against the Audit entity.

https://www.365knowledge.com/2019/03/06/dynamics-365-user-access-report-with-fetchxml-and-power-bi/

• Do we have the Audit entity available in Azure Synapse Link (Export to Data Lake)? >> No

How to set up Azure Synapse Link >>

https://nishantrana.me/2021/06/16/how-to-setup-azure-synapse-link-microsoft-dataverse/

• Cannot we write a Fetch XML Based SSRS report?

Writing a Fetch XML based-report would be challenging, because of the way information is saved.

Check the below article to understand how the audit table stores the information.

https://mahadeomatre.blogspot.com/2015/02/ms-crm-audit-database-table-details.html

http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

• How about SQL Based SSRS Report if we are using SQL – for On-Premise?

Refer to the below article that provides the steps to do so.

http://makdns.blogspot.com/2014/06/dynamic-crm-20112013-audit-report-in.html

Hope it helps..