Kusto Group By Query

Question

I have some log data structured like:

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

I need to return the latest record for each "dataName" by location/subLocation.

e.g.

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

Basically perform a "group by" on fields: location,sublocation, and dataName where max(timestamp)

How can I achieve this with Kusto as there is no group by support and the summarize function seems to require an aggregate function?

Yoni L. Yoni L. · Accepted Answer · 2020-05-12T20:37:50

You could try extending the dynamic properties into calculated columns, then summarizing using arg_max() to get the latest (according to timestamp) by location, subLocation and dataName.

For example:

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

Kusto Group By Query

2 Answers