I just started to use the Kusto query language. Still trying to grasp all of it.
So I have a query to get some SignIn events with a timestamp. But I'm only interested in the unique values with the most recent date.
Distinct is not an option because all rows are different due to this timestamp. Also the query returns too many results so it can't be processed.
Query to get all the logs is:
SigninLogs
| project TimeGenerated, Identity, UserPrincipalName, Location, DeviceDetail
You should be able to use the arg_max() aggregation function: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction.
And if that is done frequently - consider creating a materialized view with that logic: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/materialized-views/materialized-view-overview
For example:
datatable(a:int, b:int, c:string, d:datetime)
[
1, 2, "3", datetime(2021-09-08 15:05:10),
4, 5, "6", datetime(2021-09-08 15:05:17),
4, 5, "6", datetime(2021-09-08 15:05:43),
1, 2, "3", datetime(2021-09-08 15:05:27),
1, 2, "4", datetime(2021-09-08 15:05:53),
]
| summarize arg_max(d, *) by a, b, c
| a | b | c | d |
|---|---|---|---|
| 1 | 2 | 3 | 2021-09-08 15:05:27.0000000 |
| 4 | 5 | 6 | 2021-09-08 15:05:43.0000000 |
| 1 | 2 | 4 | 2021-09-08 15:05:53.0000000 |