CODEWITHSUNDEEP
kqlazure-log-analytics-workspace
M. Köppen
M. Köppen

Reputation: 11

Kusto - All data per id for max date

Kusto - All data per id for max date

Hi, I am struggeling with a query and hope someone can help me with this topic. :) I want to get all data per ID related to the latest timestamp. My source looks something like this:

Timestamp ID Other columns
Date A ID A other data 1
Date A ID A other data 2
Date B ID B other data 1
Date B ID B other data 2
Date C ID A other data 1
Date C ID A other data 2
Date D ID B other data 1
Date D ID B other data 2

As result i want:

Timestamp ID Other columns
Date A ID A other data 1
Date A ID A other data 2
Date B ID B other data 1
Date B ID B other data 2

So for the ID A and B (and so on) all rows with the same (max)timestamp related to the ID.

I tried --> source | summarize arg_max(timestamp) by ID

That results in only in:

Timestamp ID Other columns
Date A ID A other data 1
Date B ID B other data 1

If I add further columns to the summarize i got a amount of rows depending of the different entries in the other column, but also with timestamps that are not the latest. query: source | summarize arg_max(timestamp) by ID, other column

result:

Timestamp ID Other columns
Date A ID A other data 1
Date A ID A other data 2
Date B ID B other data 1
Date B ID B other data 2
Date C ID A other data 1
Date C ID A other data 2
Date D ID B other data 1
Date D ID B other data 2

Hopes that is understandle. I am grateful for any input

Thanks in advance

Marcus

Upvotes: 0

Views: 1173

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44981

Option 1.
datatable(Timestamp:int, ID:string, OtherColumns:string)
[
    4 ,"A" ,"other data 1"
   ,4 ,"A" ,"other data 2"
   ,3 ,"B" ,"other data 1"
   ,3 ,"B" ,"other data 2"
   ,2 ,"A" ,"other data 1"
   ,2 ,"A" ,"other data 2"
   ,1 ,"B" ,"other data 1"
   ,1 ,"B" ,"other data 2"
]
|   partition hint.strategy=native by ID
    (
            order by Timestamp desc
        |   extend  rr = row_rank(Timestamp)
        |   where rr == 1
        |   project-away rr
    )
Timestamp ID OtherColumns
4 A other data 1
4 A other data 2
3 B other data 1
3 B other data 2

Fiddle

Option 2.
let t = datatable(Timestamp:int, ID:string, OtherColumns:string)
[
    4 ,"A" ,"other data 1"
   ,4 ,"A" ,"other data 2"
   ,3 ,"B" ,"other data 1"
   ,3 ,"B" ,"other data 2"
   ,2 ,"A" ,"other data 1"
   ,2 ,"A" ,"other data 2"
   ,1 ,"B" ,"other data 1"
   ,1 ,"B" ,"other data 2"
];
t
|   summarize Timestamp = max(Timestamp) by ID
|   join kind=inner t on ID, Timestamp
|   project-away *1
ID Timestamp OtherColumns
A 4 other data 1
A 4 other data 2
B 3 other data 1
B 3 other data 2

Fiddle

Upvotes: 0

Related Questions