32

I need to query data from lambda using AWS Cloudwatch log insights. The query syntax provide by aws doesn't have distinct.

Only support (count_distinct(fieldname))

ref. https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/CWL_QuerySyntax.html

Example data

Column # @timestamp @ message

1 2020-02-17T13:33:29.049+07:00 [INFO] 2020 Partition key: ABC12345_A_

2 2020-02-17T11:32:29.049+07:00 [INFO] 2020 Partition key: ABC12345_B_

3 2020-02-17T11:31:29.049+07:00 [INFO] 2020 Partition key: ABC12345_B_

4 2020-02-17T11:30:29.049+07:00 [INFO] 2020 Partition key: ABC12345_C_

5 2020-02-17T11:29:29.049+07:00 [INFO] 2020 Partition key: ABC12345_A_

Expected result

1 2020-02-17T13:33:29.049+07:00 [INFO] 2020 Partition key: ABC12345_A_

2 2020-02-17T11:32:29.049+07:00 [INFO] 2020 Partition key: ABC12345_B_

4 2020-02-17T11:30:29.049+07:00 [INFO] 2020 Partition key: ABC12345_C_

If usage normal SQL syntax look like below.

select distinct(uuid) as uuid, max(time) as time from table_name group by uuid order by time desc

Community
  • 1
  • 1
PKS
  • 321
  • 1
  • 3
  • 3
  • 4
    I was able to get the distinct rows using count_distinct. You could try something similar to this: `stats count_distinct(@logStream) as IngestionTime by @ingestionTime, @logStream as LogStream | sort @ingestionTime desc | limit 10` – Rupesh Jul 13 '20 at 15:20

2 Answers2

30

You can use

| stats count(*) by fieldname

This allows to list the distinct values in fieldname.

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
sunvenka
  • 412
  • 5
  • 6
15

You can use Non-Aggregation Functions in the Stats Command like below

stats latest(@timestamp) as @latestTimestamp by @message
| display @latestTimestamp, @message
easywaru
  • 1,073
  • 9
  • 17