azure Kustos query: avg response time for Urls

Question

I have urls(more than 10k) none are identical but similar and their response times. For example:

URL                                                   Response time(ms)
https://abc@example.com/order/pick/123543               291
https://abc@example.com/deliver/open/1235               589
https://abc@example.com/reach/destination/12351223      390
https://abc@example.com/pack/box/square/12355444        771
https://abc@example.com/pack/box/square/343433          750
https://abc@example.com/order/1235321                   774
https://abc@example.com/order/size/                     433

I want to summarise and render it as time graph. Please help me to count by specific name from URL eg: average response time for all the urls contains 'box' or 'order/size'

Please help me form a query.

Answer 1

This should do what you want:

datatable(URL:string,ResponseTimeInMs:long) [
    "https://abc@example.com/order/pick/123543",291,
    "https://abc@example.com/deliver/open/1235",589,
    "https://abc@example.com/reach/destination/12351223",390,
    "https://abc@example.com/pack/box/square/12355444",771,
    "https://abc@example.com/pack/box/square/343433",750,
    "https://abc@example.com/order/1235321",774,
    "https://abc@example.com/order/size/",433
]
| extend PartialUrl = tostring(split(URL, "/")[3])
| summarize avg(ResponseTimeInMs) by PartialUrl
| order by avg_ResponseTimeInMs desc

Output:

|------------|----------------------|
| PartialUrl | avg_ResponseTimeInMs |
|------------|----------------------|
| pack       | 760.5                |
| deliver    | 589                  |
| order      | 499.333333333333     |
| reach      | 390                  |
|------------|----------------------|

Or, if you want order and order/size separately, then instead of extend PartialUrl = tostring(split(URL, "/")[3]) write extend PartialUrl = replace("[0-9]*", "", replace("https://abc@example.com/", "", URL)), and you'll get the following output:

|--------------------|----------------------|
| PartialUrl         | avg_ResponseTimeInMs |
|--------------------|----------------------|
| order/             | 774                  |
| pack/box/square/   | 760.5                |
| deliver/open/      | 589                  |
| order/size/        | 433                  |
| reach/destination/ | 390                  |
| order/pick/        | 291                  |
|--------------------|----------------------|