Microsoft Sentinel – Configuring PowerBI for Advanced Reporting

Last Updated: 29/10/2024

PowerBI with Microsoft Sentinel

Lets combine two powerful solutions to give us advanced reporting by configuring PowerBI with Microsoft Sentinel. Given that Microsoft Sentinel is capable of ingesting logs from various sources, it makes sense to utilise this data to provide insights to your environment. Of course, Workbooks can be utilised for this, especially as there is so much community and out of the box content available. In some cases, we might want to take our analytics a little bit further. And for that, we can use PowerBI to query data in our Sentinel workspace to create complex and bespoke reports.

Obtain the M Query from Microsoft Sentinel

The steps for pulling data into PowerBI is quite straightforward thanks to Microsoft building in the functionality to Sentinel. You can export your KQL queries into a PowerBI M Query from the ‘Logs’ section of PowerBI.

Sentinel Query Identity Location Map KQL

Link to the KQL Query in GitHub: Sentinel/KQL/EntraIDGeoLocation.kql at main · gennaromigliaccio/Sentinel

Please note, that the contents of data returned in the query will be pulled into PowerBI. Therefore its worth using the ‘Project’ operation to only output the columns of the table that you need. In the example above, I have queried the SignInLogs and only projected the columns that I need in PowerBI. Whilst you can pull in all data and manipulate in PowerBI, for larger volumes of data will cause the query to take longer, possibly impacting performance. It also has the chance to expose more data via PowerBI than is needed.

Once you have your KQL Query, drop down the ‘Share’ option and select ‘Export to Power BI (as an M query)’. This will prompt a download of the query to a .txt file.

Import to PowerBI

In your PowerBI application (I used the PowerBI Desktop version for this example), click on ‘Get data’ from the top bar and select ‘Blank Query’.

This will open up the query editor, towards the top, click on ‘Advanced Editor’ to bring up the query screen.

PowerBI Advanced Editor

You can paste the contents of the downloaded M Query (saved as a .txt file) into the editor. Click ‘done’ and close the editor. You should have something that looks like the following:

Advanced Query PowerBI Sentinel KQL Location

Note: You can adjust the query timespan from the query itself, by adjusting the highlighted value. For the example above, this is set at 7 days (P7D), if you adjust this to ‘P30D’ this will adjust it to 30 days.

Authenticate to the Data Source

You will then get a prompt that will indicate that you need to authenticate to the data source. In the sign in prompt, select ‘organizational account’ and then click ‘Sign in’, this will then bring up the familiar Microsoft login window. Authenticate with an account that has the relevant permissions to access Sentinel and the underpinning Log Analytics Workspace.

Microsoft Authentication Screen

Once you authenticate, the query will run and you will start to see data populated in PowerBI.

PowerBI Data KQL Entra Sentinel

Create your Visuals in PowerBI

Now that you have the data in PowerBI, you can create your visuals in the usual way. For my example, I used the map visual and the Latitude and Longitude fields to populate it.

PowerBI Map Visual

This will create a simple map and show where users have been authenticating based on the Longitude and Latitude of the IP address.

You can of course create all sorts of visuals with PowerBI depending on what you want to display and analyse. 

PowerBI Dashboard Sentinel KQL Location Map Authentication

Hopefully this serves as a useful starting point for using PowerBI with Microsoft Sentinel. If anyone has any ideas of what dashboards to create then please pop it in the comments. I am always on the lookout for ideas of what data to display! 

If your interested in getting started with Microsoft Sentinel, please visit my other article for planning your implementation of Microsoft Sentinel.

7 thoughts on “Microsoft Sentinel – Configuring PowerBI for Advanced Reporting

Leave a Reply

Your email address will not be published. Required fields are marked *