Application Insights: Analytics - how to extract a row in a specific place

Question

I'd like to do,

• Retrieving query strings, where param = 1, as indicated in "2."
• Getting pageViews in Google Analytics with a table like "3."

1. Actual URLs Included in the PageView

• https://example.com/dir01/?query=apple¶m=1
• https://example.com/dir01/?query=apple¶m=1
• https://example.com/dir01/?query=lemon+juice¶m=1
• https://example.com/dir01/?query=lemon+juice¶m=0
• https://example.com/dir01/?query=tasteful+grape+wine¶m=1

2. Expected value to retrieve

• apple
• lemon+juice
• tasteful+grape+wine

3. Expected Result in AI Analytics

• Request Parameters | count
  • apple | 2
  • lemon + juice | 1
  • tasteful + grapes + wine | 1

Tried to do

https://docs.microsoft.com/en-us/azure/application-insights/app-insights-analytics-reference#parseurl

https://aka.ms/AIAnalyticsDemo

I think that extractor parseurl(url)should be useful. I tried the latter parseurl(url), but don’t know how to extract the “Query Parameters” as a single column.

pageViews
| where timestamp > ago(1d)
| extend parsed_url=parseurl(url)
| summarize count() by tostring(parsed_url)
| render barchart 

• url
  • http://aiconnect2.cloudapp.net/FabrikamProd/
• parsed_url
  • {"Scheme":"http","Host":"aiconnect2.cloudapp.net","Port":"","Path":"/FabrikamProd/","Username":"","Password":"","Query Parameters":{},"Fragment":""}

Answer 1

, parseurl - . , json. "" :

pageViews
| where timestamp > ago(1d)
| extend parsed_url=parseurl(url)
| extend query = tostring(parsed_url["Query Parameters"]["query"])

:

pageViews
| where timestamp > ago(1d)
| extend parsed_url=parseurl(url)
| extend query = tostring(parsed_url["Query Parameters"]["query"])
| extend param = toint(parsed["Query Parameters"]["param"])
| summarize sum(param) by query

, :

let vals = datatable(url:string)["https://example.com/dir01/?
query=apple&param=1", "https://example.com/dir01/?query=apple&param=1", 
"https://example.com/dir01/?query=lemon+juice&param=1", 
"https://example.com/dir01/?query=lemon+juice&param=0", 
"https://example.com/dir01/?query=tasteful+grape+wine&param=1"];
vals
| extend parsed = parseurl(url)
| extend query = tostring(parsed["Query Parameters"]["query"])
| extend param = toint(parsed["Query Parameters"]["param"])
| summarize sum(param) by query

, ,