I have a table of analytics events for our app that include UserID and the version. The data is in Azure AppInsights and I need to use Kusto query.
An example of the data is
| UserID | application_Version |
|---|---|
| 07603a38-dfec-4bdb-941c-fd990d973fef | 3.8.0 |
| 07603a38-dfec-4bdb-941c-fd990d973fef | 3.8.0 |
| 07603a38-dfec-4bdb-941c-fd990d973fef | 3.9.0 |
| a17719f2-3739-4050-bbad-bc75e6b063df | 3.9.0 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.1.0 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.8.0 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.8.0 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.8.0 |
| 9fcc5ef2-5c61-48f4-b0cc-2f4e69851f5e | 3.9.0 |
I can group by UserId and show a count across app versions OR I can group by app version with a count across all users.
However what I want it to group by UserId and within that I want a count for each version. The desired table output would be for the data above
| UserID | application_Version | Count |
|---|---|---|
| 07603a38-dfec-4bdb-941c-fd990d973fef | 3.8.0 | 2 |
| 07603a38-dfec-4bdb-941c-fd990d973fef | 3.9.0 | 1 |
| a17719f2-3739-4050-bbad-bc75e6b063df | 3.9.0 | 1 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.1.0 | 1 |
| e1e260fc-010d-457a-96f0-d126c410c79f | 3.8.0 | 3 |
| 9fcc5ef2-5c61-48f4-b0cc-2f4e69851f5e | 3.9.0 | 1 |
This query does counts of app versions across all users but I want it split by user and version. How do i summarize across both fields?
customEvents
| extend Properties = todynamic(tostring(customDimensions.Properties))
| extend UserID = Properties.UserID
| where application_Version !in~ ("4.0.2", "4.1.0", "4.0.1")
| summarize count(UserID) by application_Version
Thanks to @Peter Bons for pointing out the painfully obvious. I now have this working.
I've also removed the case insensitive syntax. The query is now
customEvents
| extend Properties = todynamic(tostring(customDimensions.Properties))
| extend UserID = Properties.UserID
| where application_Version !in ("4.0.2", "4.1.0", "4.0.1") and isnotnull(UserID) and timestamp > ago(60d)
| summarize count() by tostring(UserID), application_Version