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?
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 |