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.
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.
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:
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.
Once you authenticate, the query will run and you will start to see data populated in PowerBI.
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.
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.
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.
Great post G!
Thanks Andy!
Nice article 👍 would you mind posting the KQL query used please?
Thanks Jack, I will update the post shortly with the KQL query 🙂
Hi Jack, I have added in the link to my GitHub for the KQL query. Popping it here for ease: https://github.com/gennaromigliaccio/Sentinel/blob/main/KQL/EntraIDGeoLocation.kql
Thank you!
🙏