Application Insights (AI) and Log Analytics (LA) from Microsoft Azure provide easy and inexpensive ways to instrument applications. Using just an instrumentation key, any application can send operational data to AI which can then provide a rich array of tools to monitor the operation of the application. In fact, the blog that you are reading uses an Application Insights plugin for WordPress that registers each view of a page into an instance of AI in my Azure tenant.
Application Insights data can be queried directly in the Azure portal to provide rich insights. In addition, the data can be exported to Excel for further analysis, or, it can be queried using Power Query in either Excel or Power BI. The procedure for using Power Query can be found in this article. The approach for doing so, uses the Web connector in Power Query, which can be automatically refreshed on a regular basis. The Web connector does not however support Direct Query, so the latency of the data in this scenario will be limited by the refresh schedule configured in Power BI. Any features that depend on Direct Query (Aggregations, Automatic Page Refresh) will also not work.
If you’ve worked with AI or LA, and dropped down to the Query editor, you’ve been exposed to KQL – The Kusto Query Language. This is the language that is used by Azure Data Explorer (ADX), or as its code name, “Kusto”. This is of course not a coincidence, as the Kusto engine powers both AI and LA.
Power BI contains a native connector for ADX, and if you can configure an ADX cluster for yourself, populate it, and work with it in Power BI for both imported and Direct Query datasets. Given that ADX is what powers AI and LA, it should be possible to use this connector to query the data for AI and LA. It turns out that the introduction of a new feature known as the ADX proxy will allow us to do just that.
The ADX proxy is designed to allow the ADX user interface to connect to instances of AI and LA and run queries from the same screens as native ADX clusters. The entire process is described in the document Query data in Azure Monitor using Azure Data Explorer. The document explains the process, but what we are particularly interested in is the syntax used to express an AI or LA instance as an ADX cluster. Multiple variations are described in the document, but the ones that we are most interested in are here:
For LA: https://ade.loganalytics.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.operationalinsights/workspaces/<workspace-name>
For AI: https://ade.applicationinsights.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.insights/components/<ai-app-name>
By substituting in your subscription ID, resource group name, and resource name, you can treat these resources as if they were ADX clusters, and query them in Power BI using Direct Query. As an example, a simple query on this blog can be formed using the ADX connector:
And the result will appear as:
The precise query is provided in the query section of the connector above.
Once the report is built, it can be deployed to the PowerBI service, and refreshed using AAD credentials.
It is important to note that this method does NOT require you to configure an ADX cluster of your own. We are simply utilizing the cluster provided to all instances of AI and LA. We therefore do not have any control over performance levels, as we would have in a full ADX cluster. However, if the performance is adequate,(and the queries designed appropriately), this can be a good approach to work with AI and LA data that has low latency (near real time) requirements.
Hello,
When I’m trying to connect using the ADX format url with Log Analytics in Power BI error below came up when placing the credentials. What might be the error or issue when this occurs?
[DataSource.Error] Non ADX/Kusto URL
I was kinda looking for this guide. Tried before with the ADX cluster that was unnecessary overload. Thanks for sharing!
I tried to use application insights url using subscriptionid ,resource group and resource name using below url
https://ade.applicationinsights.io/subscriptions/cd3e13-8bed-4f92-9fa-1feef5c19025/resourcegroups/dataAnomalyapp/providers/microsoft.insights/components/dataanomalytoolv1
when we connect the dataexplorer throwing below error messge could you please help me how to gidrid of this error
[DataSource.Error] WWW-Authenticate was not found in response headers
Same problem as Raveendra ….
Hey folks. It appears that there has been a regression introduced into the ADX connector that causes this issue. It happens with versions of Power BI Desktop from July to the present. The workaround is to replace the generated “AzureDataExplorer.Contents” in your Power Query (using the Advanced editor) with “Kusto.Contents”. I have been told that this will be remedied in an upcoming version of the Desktop, but I haven’t seen it yet. The tricky part is getting to the M editor at all, but one way to do it is to connect Power BI to an actual ADX cluster database, then do the replace above, as well as replacing the cluster name with the AI/LA proxy cluster.
It is much easier to use a Blank query as outlined here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/application-insights#export-data-to-power-bi
Using the blank query approach (which is perfectly valid) uses the web connector, and therefore does not allow Direct Query access, which is the point of this article. If you need DQ access to AI/LA, this is how.
Gotcha. I’m new to PBI, didn’t really know what DQ was. I came to this article by trying to do what I ended up accomplishing with Blank Query, figured it may help someone in the same situation. I tried it your way and was not able to make it happen. Probably doing something wrong, but again, BQ is simple and did what I need.
Hi John,
Thank you for your really helpful article.
I am able to connect to app insight as you suggested here in below comment,
Hey folks. It appears that there has been a regression introduced into the ADX connector that causes this issue. It happens with versions of Power BI Desktop from July to the present. The workaround is to replace the generated “AzureDataExplorer.Contents” in your Power Query (using the Advanced editor) with “Kusto.Contents”. I have been told that this will be remedied in an upcoming version of the Desktop, but I haven’t seen it yet. The tricky part is getting to the M editor at all, but one way to do it is to connect Power BI to an actual ADX cluster database, then do the replace above, as well as replacing the cluster name with the AI/LA proxy cluster.
But the problem here ,i faced is i am not able to do parse JSON . Means it doesnot give me the error but it is not spillit the columns. If i use the same app insight query by using the web.content connector, it is parsing that column to the Json(splits the column in a different column)
Can you please help me in this?
Hi John,
Thank you for your really helpful article.
I am able to connect to app insight as you suggested here in comment,
But the problem here ,i faced is i am not able to do parse JSON . Means it doesnot give me the error but it is not spillit the columns. If i use the same app insight query by using the web.content connector, it is parsing that column to the Json(splits the column in a different column)
Can you please help me in this?
How do you get into the advanced query editor to make this work? I can’t get out of the main ‘get data’ window. I’m trying to hit a log analytics workspace via this cluster value:
https://ade.loganalytics.io/subscriptions/d54c2bbe-4ae3-4883-bc57-0a05f8dee87c/resourcegroups/rg-appservicelogs-cus/providers/microsoft.operationalinsights/workspaces/AppServicesWorkSpace
It does take me to the second page, but when I try to sign in, I get an error stating
the resource principal named …… was not found.
I’m able to connect fine from the data explorer site, just not from inside of Power BI.
How do you get into the advanced editor while trying to connect?
With Azure Data Explorer connector, I’ve created a “fake” connection to “https://help.kusto.windows.net” specifying “Direct Query” and selecting the database and a random table. After the model creation, in Advanced Editor, i’ve replaced “AzureDataExplorer.Contents” with “Kusto.Contents”. It is still a Direct Query?
…. i can see “Direct Query” bottom right in power bi desktop, so i suppose yes!
I can’t seem to connect to my Application Insights using the Azure Data Explorer connector in Power BI. When I use the exact same uri in https://dataexplorer.azure.com/ it connects flawlessly. Did they break third party connections to this endpoint?
invalid_resource: AADSTS500011: The resource principal named https://ade.applicationinsights.io/subscriptions/…/resourcegroups/…/providers/microsoft.insights/components/… was not found in the tenant named …. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You might have sent your authentication request to the wrong tenant.
Trace ID: …
Correlation ID: …
Timestamp: 2021-04-02 13:37:55Z.
I’m having the same issue as Wouter. I’m sure this is some simple configuration problem or permissions issue, but if someone has the quick answer it might save me some troubleshooting time.
I’m getting the same issue as Wouter. Hoping it’s a simple fix.
@John White – Any thoughts on fixing the error that Wouter mentioned above?
Hey folks
I’ve been seeing the same behaviour myself. The way that I work around it is as follows (this assumes coming at it totally from scratch).
1. Create a new data connection to an Azure Data Explorer database. Anything – it doesn’t matter what.
2. Ensure that you select Direct Query for the ADX source when prompted.
3. Use the Advanced Editor in Power Query to replace the Cluster URL with the URL of the LA or AI source. Remember to use the workspace name or app name as the database name.
4. Replace “AzureDataExplorer.Contents” with “Kusto.Contents” at the beginning of the expression.
5. Authenticate using your credential, and you should be good to go. I just did this this morning successfully.
Good luck!
This solution works, but once I drill down into a table I get the following exception. I had contact with a team at Microsoft via an email address on the page linked in the article, but they could not get to the bottom of it.
Bad request: ApiContractViolationError: management command parsing failed with error: SyntaxError: Expected end of input but “|” found., Location: line 1, column 17
Details:
Error=ApiContractViolationError: management command parsing failed with error: SyntaxError: Expected end of input but “|” found., Location: line 1, column 17
Code=MgmtCslFailedParsing
Type=adxproxy.Exceptions.MgmtCslFailedParsing
x-ms-activity-id=3f168920-dd74-43df-a1b0-26cfebb2cd15
x-ms-client-request-id=KPBI;bd5e2eee-7abd-4c99-99e5-f4b40cc827ef;2cdfd040-4a25-43ee-bd4d-2b37f6a643c8;d204cddc-79f9-468d-ab24-6e9bf14b23d3
Wouter – try passing the Kusto query directly – don’t use the inbuilt Power Query Navigate. That works around that problem. Tricky – yes… but doable.
Thank you John, I finally got it!
To others struggling, the query should look something like the following in the advanced editor. You need to fill in your own subscription guid and application insights instance name. Note than “customEvents” is a Kusto query and if you need to expand filter or do whatever that is the place where you should do it, not in the Power Query Editor GUI. The easiest way to mess around with these Kusto queries is to try things out at https://dataexplorer.azure.com/
let
Source = Kusto.Contents(“https://ade.applicationinsights.io/subscriptions/…subguid…”, “…applictioninsightsname…”, “customEvents”, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
in
Source
For people still facing issues..
the above method will not work exactly as it is.
You need to create a Azure Data explorer cluster, DB and a table inside that.
Connect to this table from power bi.
Go to advance editor after the load. Remove the data inside that and paste the below code. Note: this is for Log analytics workspace.
let
Source = Kusto.Contents(“https://ade.loganalytics.io/subscriptions/ 000000000000000000000000000/resourcegroups/loganalytics/providers/microsoft.operationalinsights/workspaces/LOGGINGDETAILS”, “LOGGINGDETAILS”, “ADFActivityRun”, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
in
Source
When changing to Kusto.Contents is the data still pulled in via direct query? In my dataset it seems like it reverted to import mode…
@Mattias
I think you might have missed to check the direct query box while the connection was created. Please redo it and make sure the direct query radio button is on.
Can I connect it to https://ade.loganalytics.io/subscriptions/617cff91-37aa-40e6-a9cf-97bac2b77d1d/resourcegroups/rg-aks-lamp-prod-000/providers/microsoft.operationalinsights/workspaces/log-lamp-prod-HFIdH98i
from POWERBI using Azure Data Explorer(Kusto) connector?
My client is outside my organisation?I need it as Direct QUERY
when I tried to connect LA proxy Cluster I am getting below error from PowerBI using Kusto Connector i am getting below error
invalid_resource: AADSTS500011: The resource principal named https://ade.loganalytics.io/subscriptions/617cff91-37aa-40e6-a9cf-97bac2b77d1d/resourcegroups/rg-aks-lamp-prod-000/providers/microsoft.operationalinsights/workspaces/log-lamp-prod-HFIdH98i was not found in the tenant named #####.onmicrosoft.com. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You might have sent your authentication request to the wrong tenant.
Trace ID: daba1084-33aa-4135-8959-4698c6c75f00
Correlation ID: 6140330c-ca97-4b86-9657-4270ad7ce736
Timestamp: 2021-09-10 10:04:15Z.
If i use Kusto.Contents(“https://ade.loganalytics.io/subscriptions/#####/resourcegroups/######/providers/microsoft.operationalinsights/workspaces/#######”
istead of
Kusto.Contents(“https://ade.applicationinsights.io/subscriptions/########/resourcegroups/#######/providers/microsoft.insights/components/#######
it reverts back to IMPORT.
I’m not able to use direct query with log analytics.
Hello folks,
I am trying to make a Direct Query connection following above instructions. Although the error I’m receiving is “Access Denied”, whenever I’m trying to authenticate. Anyone knows how to solve this? Does is it mean that the query is OK but I just don’t have the right authentication. If so, what is required for this kind of connection?
Thanks in advance,
Ruben