Application Insights: Analytics - how to extract string at specific position

grantaka36 picture grantaka36 · Apr 4, 2017 · Viewed 9.2k times · Source

I'd like to do,

  • Extracting "query" strings where param=1 as follows in "2."
  • Getting pageViews in Analytics with table as "3."

1. Actual urls included in pageView

  • 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

2. Value expected to extract

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

3. Expected output in AI Analytics

  • Query Parameters | Count
    • apple | 2
    • lemon+juice | 1
    • tasteful+grape+wine | 1

image

Tried to do

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

https://aka.ms/AIAnalyticsDemo

I think extract or parseurl(url) should be useful. I tried the latter parseurl(url) but don't know how to extract "Query Parameters" as one 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

Asaf Strassberg picture Asaf Strassberg · Apr 4, 2017

Yes, parseurl is the way to do it. It results in a dynamic value which you can use as a json. To get the "query" value of the query parameters:

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

and to summarize by the param value:

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

You can see how it works on your example values in the demo portal:

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

Hope this helps,

Asaf