CODEWITHSUNDEEP
azure-application-insightskql
ezG
ezG

Reputation: 421

Having trouble selecting rows using KQL (Kusto)

I am trying to select rows based on the timestamp. In the sample data that follows, some columns contain duplicate computer names. I am interested in the row with the latest timestamp.

+------------------------+----------+---------+------------+
|        TIMEST  AMP     | COMPUTER | VERSION | MORE COLS. |
+------------------------+----------+---------+------------+
|  2019-10-02 10:32:40   | COMPA    |  1234   |  ...       |
+------------------------+----------+---------+------------+
|  2019-09-12 11:15 23   | COMPA    |  1235   |  ...       |
+------------------------+----------+---------+------------+
|  2019-11-13 15:23:25   | COMPA    |  1234   |  ...       |
+------------------------+----------+---------+------------+
|  2019-10-02 10:32:40   | COMPB    |  1234   |  ...       |
+------------------------+----------+---------+------------+
|  2019-09-13 11:15 23   | COMPC    |  1235   |  ...       |
+------------------------+----------+---------+------------+
|  2019-11-13 15:23:25   | COMPC    |  1235   |  ...       |
+------------------------+----------+---------+------------+

The following result should be returned

+------------------------+----------+---------+------------+
|        TIMEST  AMP     | COMPUTER | VERSION | MORE COLS. |
+------------------------+----------+---------+------------+
|  2019-11-13 15:23:25   | COMPA    |  1234   |  ...       |
+------------------------+----------+---------+------------+
|  2019-10-02 10:32:40   | COMPB    |  1234   |  ...       |
+------------------------+----------+---------+------------+
|  2019-11-13 15:23:25   | COMPC    |  1235   |  ...       |
+------------------------+----------+---------+------------+

It looks like a nested query should work. I found an example, but I'm not sure how to get it to work with this data

SAMPLE

dependencies
| where resultCode == toscalar(
  dependencies
  | where resultId == 7
  | top 1 by timestamp desc
  | project resultCode)

Upvotes: 1

Views: 336

Answers (1)

Yoni L.
Yoni L.

Reputation: 25965

you could try using summarize arg_max() (doc):

datatable(timestamp:datetime, computer:string, version:int)
[
    datetime(2019-10-02 10:32:40), 'COMPA', 1234,
    datetime(2019-09-12 11:15:23), 'COMPA', 1235,
    datetime(2019-11-13 15:23:25), 'COMPA', 1234,
    datetime(2019-10-02 10:32:40), 'COMPB', 1234,
    datetime(2019-09-13 11:15:23), 'COMPC', 1235,
    datetime(2019-11-13 15:23:25), 'COMPC', 1235,
]
| summarize arg_max(timestamp, *) by computer

-->

| computer | timestamp                   | version |
|----------|-----------------------------|---------|
| COMPA    | 2019-11-13 15:23:25.0000000 | 1234    |
| COMPB    | 2019-10-02 10:32:40.0000000 | 1234    |
| COMPC    | 2019-11-13 15:23:25.0000000 | 1235    |

Upvotes: 2

Related Questions