Azure Application Insights Analytics Query join by parsed url and summarize

Question

I need to combine requests and customMetrics tables by parsed url. On output it should have common parsed url, avg duration of requests and avg value of requests from CustomMetrics.

This code doesn't work ^(

let parseUrlOwn = (stringUrl:string) {
 let halfparsed = substring(stringUrl,157);
 substring(halfparsed,0 , indexof(halfparsed, "?"))
};

customMetrics 
| where name == "Api.GetData" 
| extend urlURI = tostring(customDimensions.RequestedUri) 
| extend urlcustomMeticsParsed = parseUrlOwn(urlURI)
| extend unionColumnUrl = urlcustomMeticsParsed
| summarize summaryCustom = avg(value) by unionColumnUrl
| project summaryCustom, unionColumnUrl
| join (
   requests
   | where  isnotempty(cloud_RoleName)
   | extend urlRequestsParsed = parseUrlOwn(url)
   | extend unionColumnUrl = urlRequestsParsed
   | summarize summaryRequests =sum(itemCount), avg(duration)
   | project summaryRequests, unionColumnUrl
) on unionColumnUrl

Answer 1

Instead of inventing your own url parsing, how about using parse_url (https://docs.loganalytics.io/docs/Language-Reference/Scalar-functions/parse_url()) and using that instead?

It also appears that your summarize line in the requests join, isn't summarizing on url, so I'm not sure how that works.

Shouldn't this line:

 | summarize summaryRequests =sum(itemCount), avg(duration)

be

 | summarize summaryRequests =sum(itemCount), avg(duration) by unionColumnUrl

like it is in the metrics part of the query. Also, why are you calculating the average in that summarize? you're just throwing it away by not projecting it on the next line.