How to implement extended audit logging in dataverse Part 2

February 8, 2023 - 4 minutes read - 688 words

Background

In my last blog post How to implement extended audit logging in dataverse Part 1 I showed how we can use the out of box Microsoft Purview/Office logging to send Retreieve events in Dataverse and then use Power BI to visualize this information back to users in Dataverse.

However, the entire process of setting everything up was quite cumbersome and involved a lot of moving parts. So I was in search for something simpler. The suggested solution is to use a generic plugin-assembly that you register on the events that you want to audit and this assembly will send information to an Application Insights of your choice. Then we can query the log analytics workspace with Power BI and show the audit log data. Here is an image showing what we are trying to achieve.

Audit Log The architecture of the suggested solution.

1. Create an Application Insights in Azure

The first thing you should do is to go to your Azure tenant and create an Application Insights app. When you have everything set up, copy your connection string, we will use this later to connect to and send Audit data to Application Insights.

Application Insights Connection string Copy the connection string for later.

2. Download the Application Insights Assembly

Now we will download the solution that I built and install it to our Dataverse environment. This solution will connect to you application insights and send the data. The source code is provided, so you can extend the assembly to your needs. The source code.

3. Register steps

When you have installed the solution in to your Dataverse environment you can register on what events the assembly should send logs to Application Insights.

On each step make sure to paste the connection string of your application insights in the secure config part (I will improve this to use Environment Variables in the future). Notice that I have registerd the step as Post Operation and asyc in order to minimize the performance impact on the system.

4. Test the logging

Perform some of the events inside Dataverse (in order to capture some data). For example, in my case, I opened a contact record. This way the Retrieve event got triggered. Now, go inside Application Insights and go to logs, select customEvents and run the query. You should see your events being logged.

Logs The event data from dataverse is registered in Application Insights.

5. Load the data to Power BI

Now we can load the data to Power BI. First go to your query in Azure and select Export -> Power BI (as an M-query). This will download a text file with the queries. Open Power BI desktop and create a new Report. Select Get data then Other -> Blank Query.

Power Bi Blank Query How to get the data from Application Insights in to Power BI.

Select advanced editor and paste the query from the doenloaded text file.

Blank Query Advanced Editor Select advanced editor.

Power Query Editor Paste your query here.

Above steps will load the logs. Next we need to select the fields from the customDimensions. In order to do this we need to right click the customDimensions field and transform it to JSON. This will transform each rows column to a record.

Transform to JSON Transform the customDiemensions filed to JSON.

Split the record to its properties by pressing the button shown in below image.

6. Power BI report - End result

Now you can create the Power BI report. Below is a picture of the end result.

The end result, a power bi report that can be embeded and filtered in dataverse.

The report can be embeded inside Dataverse in similar fashion as explained in part 1 - 5. Visualize data in Power Bi and embed in Dataverse with filter.

Conclusion

Here you got to see yet another way to create an extended audit log. I actually prefer this solutions, since there is fewer moving parts in it. What solution do you like best? Feel free to contact me on twitter with your opinions.