azure-data-explorer - Kusto 按查询分组

Question

我有一些日志数据结构如下:

{
    timestamp: mm/dd/yyy hh:mm:ss
    customDimensions : {
      location: "locationA",
      subLocation: "subLocationB",
      dataName: "dataNameB",
      data: "value"
    } 
}

我需要按位置/子位置返回每个“dataName”的最新记录。

例如

locationA sublocationB dataName value
locationA sublocationC dataName value
locationA sublocationB dataname2 value

基本上对字段执行“分组依据”:location、sublocation 和 dataName 其中 max(timestamp)

我如何使用 Kusto 实现这一点，因为没有支持组，并且汇总函数似乎需要聚合函数？

Answer 1

您可以尝试将动态属性扩展到计算列中，然后使用 arg_max() 进行汇总。通过 timestamp 获取最新的(根据 location ) , subLocation和 dataName .

例如:

datatable(d:dynamic)[
    dynamic({
    "timestamp": "2020-05-12 20:32:08",
    "customDimensions" : {
      "location": "locationA",
      "subLocation": "subLocationB",
      "dataName": "dataNameB",
      "data": "value"
        } 
    }), dynamic({
    "timestamp": "2020-05-12 20:35:08",
    "customDimensions" : {
      "location": "locationA",
      "subLocation": "subLocationB",
      "dataName": "dataNameB",
      "data": "value"
        } 
    }), dynamic({
    "timestamp": "2020-05-12 20:25:08",
    "customDimensions" : {
      "location": "locationB",
      "subLocation": "subLocationB",
      "dataName": "dataNameC",
      "data": "value"
        } 
    })
]
| project timestamp = todatetime(d.timestamp),
          location = tostring(d.customDimensions.location),
          subLocation = tostring(d.customDimensions.subLocation),
          dataName = tostring(d.customDimensions.dataName), data = tostring(d.customDimensions.data)
| summarize arg_max(timestamp, *) by location, subLocation, dataName