CODEWITHSUNDEEP
kql
ATur
ATur

Reputation: 157

Kusto Query - Display most recent row

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

Upvotes: 6

Views: 8821

Answers (1)

Yoni L.
Yoni L.

Reputation: 25915

You should be able to use the arg_max() aggregation function: https://learn.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://learn.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

Upvotes: 4

Related Questions