How to create an Azure Kusto query to group by client OS name only (OS version removed) on App insights?

2
votes

What I want

The number of page views grouped by client OS (no OS version = only OS name) and week.

What I have - Kusto query:

pageViews
|where timestamp > ago(90d)
|summarize Browser_hits = count() by Date = startofweek(timestamp), client_Browser
|sort by Date   
|render timechart

The problem with this query is that the client OS name is coming with the version in it and that ends up in different versions grouping separately (see picture below).

enter image description here

Update

This is close to what I need but it won't work for any names, I'm just posting this as an example that helps to understand the actual question.

pageViews
|where timestamp > ago(90d)
|summarize Browser_hits = count() by Date = startofweek(timestamp), BrowserNameTrimed = substring(client_Browser,0,5)
| sort by Date   
| render timechart

With the previous query I get this (kind of cheating):

enter image description here

1
azureazure-application-insightsazure-data-explorerkql

1 Answers

5
votes

would this work? (parsing the browser name out of the "browser name + browser version" combination using the parse operator):

pageViews
| where timestamp > ago(90d)
| summarize Browser_hits = count() by Date = startofweek(timestamp), client_Browser
| parse kind=regex client_Browser with client_Browser @" \d+" *
| render timechart