An area I’ve been spending some time recently is Azure Sentinel reporting. There’s many methods and many tools available to provide reports for Azure Sentinel data. Workbooks, Playbooks, Excel, et al, provide some easy wins when a report is needed quickly. But, more and more I’m hearing from customers where they want even more flexibility to produce deeper-level reporting – particularly when it comes to developing reports to share with business leaders.
Any KQL query you develop in the Logs tool in Azure Sentinel can be exported to an M query (Power BI) and then following a few, defined steps, the same query and query results can be ready to be manipulated in Power BI. I’ll be digging a bit deeper in the next few weeks into how to work with Power BI to create some fantastic visualizations, but for today, let’s start fresh and simple, and just learn how to setup the connection and make the query results available in Power BI.
How to do it
To get started, run your query in the Logs tool and then choose to Export to a Power BI (M query).
Next, open Power BI Desktop and choose Get Data – Blank Query – and then Advanced Editor.
(If you don’t already have Power BI Desktop installed, you can download it from here: https://powerbi.microsoft.com/desktop/)
Open the M query (Notepad file) that was downloaded as part of the original export. Copy the query from the Notepad file and then paste it directly into the Advanced Editor.
You can do this over and over again with each KQL query you want to include, i.e., export, then copy/paste.
You might need to ensure your connection between Power BI Desktop and your Log Analytics workspace for Azure Sentinel is valid. I’ve had to re-login before (seems to be a quirk).
Once the data is shown in the Advanced Editor, click the Close & Apply option.
Back at the main Power BI screen, on the far right, choose the fields from your query you want to include and you can now begin applying the Visualizations.
=========================
=========================
[Want to discuss this further? Hit me up on Twitter or LinkedIn]
[Subscribe to the weekly Azure Sentinel newsletter]
