Man – that’s a boring title. But it’s accurate.
A few months ago, I posted an article outlining how to connect Power BI to Azure Application Insights and Azure Log Analytics (jointly referred to as Azure Monitor) with Direct Query. This article describes an approach that allows you to use a native Kusto connector to connect to the Azure Monitor instance as if it were an ADX cluster. This allows for Direct Query to be used, among other things. The option connecting Power BI available through the Azure Monitor UI uses an html connector to query the respective APIs, and that connector doesn’t support Direct Query.
The problem with using this connector is that it’s a bit of a hack. At the time it was written, you needed to use the old Power BI driver for Kusto to make it work, and that approach isn’t simple. Over time, it stopped working altogether for Application Insights. The ADX connector has since been updated to support connection to Azure Log Analytics (but not Application Insights) and is therefore still valid.
There is however another way to achieve this by using your own ADX cluster. ADX clusters allow for “cross-cluster queries” that permit tables in a database in one cluster to be joined or unioned with tables in a completely different cluster. The same proxy addresses mentioned above can be used in one of these cross-cluster queries, and in this way, be just use the ADX cluster as an intermediary.
Everything that you need to know about this approach can be found in the support article “Query data in Azure Monitor using Azure Data Explorer”
To create a Power BI report that queries Azure Monitor data using Direct Query, first create a new report, and connect to data using the “Azure Data Explorer (Kusto) connector”. Enter the address of the cluster, and the name of a database within that cluster. The database itself doesn’t matter; it simply provides a scope for the query. Finally, you need to specify the query, and this is where the cross-cluster query comes into the picture. The query takes the following form:
cluster(‘ProxyURL‘).database(‘ResourceName‘).TableName
The Proxy URLs differ between Log Analytics and Application Insights. The two take the following forms:
Log Analytics:
https://ade.loganalytics.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.operationalinsights/workspaces/<workspace-name>Application Insights:
https://ade.applicationinsights.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.insights/components/<ai-app-name>The cross-cluster query for the table named “pageViews” in an Application Insights instance named “WhitePagesLogs” in a Resource group named “MyResourceGroup” in the subscription “71a90792-474e-5e49-ab4e-da54baa26d5d” is therefore”
cluster('https://ade.applicationinsights.io/subscriptions/71a90792-474e-5e49-ab4e-da54baa26d5d/resourcegroups/MyResourceGroup/providers/microsoft.insights/components/WhitePagesLogs').database('WhitePagesLogs').pageViewsIt is worth explicitly noting that the resource name appears twice in the query – once in the cluster address, and as the database name.
When ready, the Get data dialog box should appear as follows:
If you want to use Direct Query, don’t forget to open the Advanced Options section, and select it here.
At this point, the report can be built, and it will behave as if it was a normal ADX cluster. You can of course build more complex queries, etc, but you cannot build things like functions, or materialized vies, since you do not have administrative access to the engine behind Azure Monitor.
Compared to using the Power BI ADX connector directly, this approach has the advantage of being explicitly supported, and it also works with bot Application Insights, and Log Analytics. On the downside, there is a cost to running your own ADX cluster, although it is minimal. This cluster is simply acting as a gateway in this case, and therefore, a bare minimum of resources will suffice.
Hello,
Thanks for this however I ran into some issues trying to use this method to connect directly to the data from application insights. I think the problem stems from the cluster/cluster address.
Following the steps in the support article linked “Query data in Azure Monitor using Azure Data Explorer”. I created a cluster using the same name as the resource but it was not recognised by Power BI .
I received this error code
[DataSource.Error] The remote name could not be resolved: ‘ClusterName.westeurope.kusto.windows.net’
Also tried to create a new cluster with a different name and got this type of error message presumably because its an empty cluster
Web.Contents failed to get contents from ‘https://Clustername.westeurope.kusto.windows.net/v1/rest/query?db=ResourceName&csl=.show%20version’ (520): InternalServiceError
and also received this error
Request is invalid and cannot be executed.
I also tried to change the table query using this url format instead
Cluster( https://adx.monitor.azure.com/subscriptions//resourcegroups//providers/microsoft.insights/components/‘).database(”)
Could you provide any additional guidance on how the cluster is supposed to be set up to make this work?
Thanks a lot.
Dear Team at bifocal.show
My name is Collin and I am a volunteer with Hope For Ukraine Charity. I have found you on Twitter
After two years since the full-scale invasion of Ukraine, at least 6 million people have fled Ukraine to escape the conflict and another 5 million people are internally displaced.
Leaving behind their homes, jobs and loved ones, Ukrainians urgently need shelter, food and water. More than 17 million people, almost half the population, require humanitarian aid.
Life for the people of Ukraine has completely changed. Fighting and indiscriminate bombing has killed families and destroyed houses, schools and hospitals. Millions in displacement centres and damaged homes face temperatures below -20C.
An estimated 1.4 million houses have been damaged by the war, and it is mostly older people and people with disabilities who remain behind in homes with leaking roofs, broken windows and no access to heating.
Your generous contributions to our Ukraine Humanitarian Crisis Appeal helped over 140,000 vulnerable women, men and children access vital aid including food, water, shelter and counselling support.
Thanks to you, our local partners are helping people to rebuild their lives after experiencing trauma, losing loved ones, homes and livelihoods.
We have also responded quickly through the Caritas network – which CAFOD is a member of – to provide shelter, food and safe spaces for families who have lost everything.
The humanitarian professionals and volunteers working on your behalf live locally and are from the communities that you are supporting. Many have been displaced by the war themselves. They understand people’s specific needs: from food and water, to shelter and clothing, to medical, psychological, and spiritual support. No two people are the same, no two crises are the same.
Thanks to your kind donations and support, we have delivered vital assistance to over 120,000 mothers, children and elderly people since the first months of the conflict. We are part of a Caritas response that has reached over 2 million refugees and internally-displaced people. By working together we are making a difference to the lives of those in greatest need.
When the emergency needs of families affected by the Ukraine conflict have been met and international attention turns to other crises, we are committed to be there in the long-term, to ensure that the communities you are helping can rebuild and heal.
How will my donation help?
More than 17 million people, almost half the current population of Ukraine, are now in need of humanitarian aid. With your help, we can keep supporting the increasing number of families who are in desperate need.
Your donation will help local experts in Ukraine to keep providing:
– food, water, and hygiene items
– shelter and clothing
– healthcare
– education
– counselling and group therapy.
Please show your support for Ukraine by donating to the below cryptocurrency wallet addresses. No donation is too small. All funds received will be used to buy humanitarian aid for the people of Ukraine. Unfortunately, we are currently not accepting bank payments as we are looking for a new banking partner.
Bitcoin: bc1q4206tlzgldnr3efu44hf9m6qm329ztzxhqfxdw
Ethereum: 0xa74cce7805342F10df39B698342380f58bB709b5
Solana: G9quVAfjmRsLfT9xpacoUp7rkFAvdYEd44kHLhzaFVRu
I thank you for your support and making a difference.
Kind regards
Collin Escamilla
phone: 403-920-2075
Address: 3092 11th Ave, Calgary, T2p 1m6, AB, Canada