Import Azure Monitor log data into Power BI
Power BI is a cloud based business analytics service from Microsoft that provides rich visualizations and reports for analysis of different sets of data. You can import the results of an Azure Monitor log query into a Power BI dataset so you can take advantage of its features such as combining data from different sources and sharing reports on the web and mobile devices.
This article was recently updated to use the term Azure Monitor logs instead of Log Analytics. Log data is still stored in a Log Analytics workspace and is still collected and analyzed by the same Log Analytics service. We are updating the terminology to better reflect the role of logs in Azure Monitor. See Azure Monitor terminology changes for details.
To import data from a Log Analytics workspace in Azure Monitor into Power BI, you create a dataset in Power BI based on a log query in Azure Monitor. The query is run each time the dataset is refreshed. You can then build Power BI reports that use data from the dataset. To create the dataset in Power BI, you export your query from Log Analytics to Power Query (M) language. You then use this to create a query in Power BI Desktop and then publish it to Power BI as a dataset. The details for this process are described below.
Be careful to optimize your query so that it doesn't take excessively long to run or it may timeout. Note the timespan value in the exported query which defines the timespan of data that the query will retrieve. Use the smallest timespan that you require to limit the amount of data that the query returns.
Create the log query in Log Analytics to extract the data for your dataset.
Select Export > Power BI Query (M). This exports the query to a text file called PowerBIQuery.txt.
Open the text file and copy its contents.
Import query into Power BI Desktop
Power BI Desktop is a desktop application that allows you to create datasets and reports that can be published to Power BI. You can also use it to create a query using the Power Query language exported from Azure Monitor.
Install Power BI Desktop if you don't already have it and then open the application.
Select Get Data > Blank Query to open a new query. Then select Advanced Editor and paste the contents of the exported file into the query. Click Done.
The query runs, and its results are displayed. You may be prompted for credentials to connect to Azure.
Type in a descriptive name for the query. The default is Query1. Click Close and Apply to add the dataset to the report.
Publish to Power BI
When you publish to Power BI, a dataset and a report will be created. If you create a report in Power BI Desktop, then this will be published with your data. If not, then a blank report will be created. You can modify the report in Power BI or create a new one based on the dataset.
Create a report based on your data. Use Power BI Desktop documentation if you're not familiar with it.
When you're ready to send it to Power BI, click Publish.
When prompted, select a destination in your Power BI account. Unless you have a specific destination in mind, use My workspace.
When the publishing completes, click Open in Power BI to open Power BI with your new dataset.
Configure scheduled refresh
The dataset created in Power BI will have the same data that you previously saw in Power BI Desktop. You need to refresh the dataset periodically to run the query again and populate it with the latest data from Azure Monitor.
Click on the workspace where you uploaded your report and select the Datasets menu.
Select the context menu next to your new dataset and select Settings.
Under Data source credentials you should have a message that the credentials are invalid. This is because you haven't provided credentials yet for the dataset to use when it refreshes its data.
Click Edit credentials and specify credentials with access to the Log Analytics workspace in Azure Monitor. If you require two-factor authentication, select OAuth2 for the Authentication method to be prompted to login with your credentials.
Under Scheduled refresh turn on the option to Keep your data up to date. You can optionally change the Refresh frequency and one or more specific times to run the refresh.