kqlazure-data-explorerkusto-explorer

Kusto - Get the value at maximum timestamp


I have some timeseries data like this:

Timestamp Resource MetricName MetricValue
T1 db1 DocumentCount 110
T2 db1 DocumentCount 120
T2 db1 DataUsage 40000
T11 db2 DocumentCount 60
T12 db2 DocumentCount 70
T21 db3 DataUsage 100000
T22 db3 DataUsage 150000

I want a KQL Query that can give me the "latest Timestamp's" MetricValue for the particular Resource/MetricName combination. Essentially, I want this:

Timestamp Resource MetricName MetricValue
T2 db1 DocumentCount 120
T2 db1 DataUsage 40000
T12 db2 DocumentCount 70
T22 db3 DataUsage 150000

How can I do this?


Solution

  • you could use the arg_max() aggregation function.

    for example:

    datatable(Timestamp:string, Resource:string, MetricName:string, MetricValue:long)
    [
        'T1',  'db1', 'DocumentCount', 110,
        'T2',  'db1', 'DocumentCount', 120,
        'T2',  'db1', 'DataUsage',     40000,
        'T11', 'db2', 'DocumentCount', 60,
        'T12', 'db2', 'DocumentCount', 70,
        'T21', 'db3', 'DataUsage',     100000,
        'T22', 'db3', 'DataUsage',     150000,
    ]
    | summarize arg_max(Timestamp, *) by Resource, MetricName
    
    Resource MetricName Timestamp MetricValue
    db1 DocumentCount T2 120
    db1 DataUsage T2 40000
    db2 DocumentCount T12 70
    db3 DataUsage T22 150000