Using Log Analytics to Connect to Power BI

Log Analytics is a basic tool for the entire Azure environment, I wrote about it before.

The possibility to access log analytics data from a tool for analysis, such as Power BI, only increases its importance. There are some options to make this access and we expect these options to improve very soon.

Using Notebooks in Azure Data Studio

That’s an interesting option, not related to Power BI: We can use notebooks in Azure Data Studio to connect to Log Analytics and create notebooks to analyse the logs.

The other options are turning this one less important, but still has its space. I wrote about this before.

Export Kusto to M/Using Web Connector

These are a small variation of the same method. Log Analytics included in an export option on the query UI to export Kusto queries to M.

Once we use the export option, a file is downloaded containing the query. We can create a blank query in Power Query and copy the content of the file, the query will work.

The exported queries also work as an example of how to use the web connector to query log analytics.

The use of the web connector is not difficult, but complex and the Power Query UI doesn’t fully generate the code. The best way to use this feature is really use the export query from Log Analytics.

There is an article about this from Microsoft.

The Web Connector only allows for Import mode. This means the data need refresh to be updated and this is a limited solution.

Use Kusto/Data Explorer Connector

Although the complexity, in my opinion this is one of the best options possible, because it allows direct query and has the flexibility to load a table and allow us to transform the data further in Power BI.

John White wrote a great article about this option, but although the comments in the article help solving the problems, the details change a lot and deserve a small review.

The Basic Concepts

Log Analytics has, in its core, the same structure than an ADX cluster. Connecting to Log Analytics is like connecting to an ADX cluster.

The article on https://docs.microsoft.com/en-us/azure/data-explorer/query-monitor-data explains how we can connect to Log Analytics from an ADX client tool. One of the most important information to get from this link is the format of the URL.

ADX has an online free client we can use to connect to clusters. You can access it on https://dataexplorer.azure.com

There are some issues with the authentication token which are not completely explained, or we are not completely sure about. If you get an error when connecting to the Log Analytics, and this may happen, the solution is simple: Make a connection first to the helper cluster – https://help.kusto.windows.net – and only after that connect to your log analytics.

The explanation – not confirmed – is the helper cluster will generate a token valid to connect to ADX clusters and this token can be used to connect to log analytics. A directly connection to log analytics doesn’t do the same.

Using in Power BI

Changes on the Data Explorer connector makes it not so simple as explained by John on his article, we can’t just use the UI to make the connection to Log Analytics.

There are different ways to achieve the result we would like, the connection. However, the result is to achieve the code below on the Power Query. In my opinion, the best solution is to start from a blank query.

= Kusto.Contents(“https://ift.tt/2RzN9cT;, “Mktlogs”, “AppServiceHTTPLogs”, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])

There are some important details to highlight in relation to this code:

• We can’t use the UI to access directly the log analytics address. The result will be “Wrong login, wrong tenant”. We need to use the UI to access the helper cluster and then change the address of the cluster.
• The default source generated by the UI, AzureDataExplorer.Contents, will not work. We need to replace it by Kusto.Contents
• Regular Power Query navigation will not work to access the data, because it sends a wrong message to the Log Analytics API. We need to include the query, which can be only the table name, directly on the source statement, as in the statement above.

Discovering the Schema

If you need to discover the available tables in your Log Analytics database, you can use the same expression replacing the table name by Null . You will retrieve a list of available tables. However, you can’t proceed with navigation, you need to choose your table and change the code.

Conclusion

Once the access and model are stablished, the amount of possible analysis we can make about any Azure and even on-premise services is huge. For example, on the image below is a small report made about access to my blog.

 

The post Using Log Analytics to Connect to Power BI appeared first on Simple Talk.

from Simple Talk https://ift.tt/3oJPii2
via