CloudWatch Insightsのクエリまとめ

# 背景・目的
- CloudWatchLogs Insightで思ったようにクエリが使えないので学習します。

# 内容
## JSON形式のログを取り扱う
### 事前準備
- 以下のログを用意します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/206276/df7c6666-1ec0-04d9-ccc5-6105e885b4ab.png)

### 検証
#### 1. デフォルトのクエリ
- まずはデフォルトのクエリをそのまま実行する。

```
fields @timestamp, @message
| sort @timestamp desc
| limit 20
```

- 結果

```
-----------------------------------------------------
|       timestamp        |        message         |
|-------------------------|-------------------------|
| 2021-11-24 14:09:30.882 | {"id":3,"name":"zzzzz"} |
| 2021-11-24 14:09:18.371 | {"id":2,"name":"yyyyy"} |
| 2021-11-24 14:08:58.528 | {"id":1,"name":"xxxxx"} |
-----------------------------------------------------
```

### 2.filter


```
fields @timestamp, @message
| filter @message like /"name":"xxxxx"/
| sort @timestamp desc
| limit 20
```
- 結果

```
-----------------------------------------------------
|       timestamp        |        message         |
|-------------------------|-------------------------|
| 2021-11-24 14:08:58.528 | {"id":1,"name":"xxxxx"} |
-----------------------------------------------------
```

### 3.項目追加
- ingestionTimeを追加します。
- ingestionTimeは、ログストリームに追加された時間になります。

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| sort @timestamp desc
| limit 20

```

- 結果

```
------------------------------------------------------------------------------------------------------------------------------------
|       timestamp        |     ingestionTime      | logStream |        message         |                 log                  |
|-------------------------|-------------------------|------------|-------------------------|---------------------------------------|
| 2021-11-24 14:09:30.882 | 2021-11-24 14:09:31.267 | pattern1   | {"id":3,"name":"zzzzz"} | 012345678901:/cloudwatch/insight/test |
| 2021-11-24 14:09:18.371 | 2021-11-24 14:09:18.895 | pattern1   | {"id":2,"name":"yyyyy"} | 012345678901:/cloudwatch/insight/test |
| 2021-11-24 14:08:58.528 | 2021-11-24 14:09:02.269 | pattern1   | {"id":1,"name":"xxxxx"} | 012345678901:/cloudwatch/insight/test |
------------------------------------------------------------------------------------------------------------------------------------
```

### 4.Parse
#### 成功版

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| parse @message  "\"id\":*," as id
| sort @timestamp desc
| limit 20

```
- 結果

```
-----------------------------------------------------------------------------------------------------------------------------------------
|       @timestamp        |     @ingestionTime      | @logStream |        @message         |                 @log                  | id |
|-------------------------|-------------------------|------------|-------------------------|---------------------------------------|----|
| 2021-11-24 14:09:30.882 | 2021-11-24 14:09:31.267 | pattern1   | {"id":3,"name":"zzzzz"} | 012345678901:/cloudwatch/insight/test | 3  |
| 2021-11-24 14:09:18.371 | 2021-11-24 14:09:18.895 | pattern1   | {"id":2,"name":"yyyyy"} | 012345678901:/cloudwatch/insight/test | 2  |
| 2021-11-24 14:08:58.528 | 2021-11-24 14:09:02.269 | pattern1   | {"id":1,"name":"xxxxx"} | 012345678901:/cloudwatch/insight/test | 1  |
-----------------------------------------------------------------------------------------------------------------------------------------
```

#### 試行錯誤
- JSONメッセージの中の抽出がうまくできない。
- JSONフィールドの区切り文字まで含めないと切り出せない。

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| parse @message  "\"name\":*" as name
| sort @timestamp desc
| limit 20

```
- 結果

```
-----------------------------------------------------------------------------------------------------------------------------------------------
|       @timestamp        |     @ingestionTime      | @logStream |        @message         |                 @log                  |   name   |
|-------------------------|-------------------------|------------|-------------------------|---------------------------------------|----------|
| 2021-11-24 14:09:30.882 | 2021-11-24 14:09:31.267 | pattern1   | {"id":3,"name":"zzzzz"} | 012345678901:/cloudwatch/insight/test | "zzzzz"} |
| 2021-11-24 14:09:18.371 | 2021-11-24 14:09:18.895 | pattern1   | {"id":2,"name":"yyyyy"} | 012345678901:/cloudwatch/insight/test | "yyyyy"} |
| 2021-11-24 14:08:58.528 | 2021-11-24 14:09:02.269 | pattern1   | {"id":1,"name":"xxxxx"} | 012345678901:/cloudwatch/insight/test | "xxxxx"} |
-----------------------------------------------------------------------------------------------------------------------------------------------
```

- 上記と同様に、JSONフィールドの区切り文字まで含めないと切り出せない。

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| parse @message  "\"id\":*" as id
| sort @timestamp desc
| limit 20
```
- 結果

```
--------------------------------------------------------------------------------------------------------------------------------------------------------
|       @timestamp        |     @ingestionTime      | @logStream |        @message         |                 @log                  |        id         |
|-------------------------|-------------------------|------------|-------------------------|---------------------------------------|-------------------|
| 2021-11-24 14:09:30.882 | 2021-11-24 14:09:31.267 | pattern1   | {"id":3,"name":"zzzzz"} | 012345678901:/cloudwatch/insight/test | 3,"name":"zzzzz"} |
| 2021-11-24 14:09:18.371 | 2021-11-24 14:09:18.895 | pattern1   | {"id":2,"name":"yyyyy"} | 012345678901:/cloudwatch/insight/test | 2,"name":"yyyyy"} |
| 2021-11-24 14:08:58.528 | 2021-11-24 14:09:02.269 | pattern1   | {"id":1,"name":"xxxxx"} | 012345678901:/cloudwatch/insight/test | 1,"name":"xxxxx"} |
--------------------------------------------------------------------------------------------------------------------------------------------------------
```

- JSONは直接フィールドを指定すると取得できるらしい（[サポートされるログと検出されるフィールド](https://docs.aws.amazon.com/ja_jp/AmazonCloudWatch/latest/logs/CWL_AnalyzeLogData-discoverable-fields.html#CWL_AnalyzeLogData-discoverable-JSON-logs)）
- 変わらず後ろの値も取れてしまう。

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| parse @message  "id" as id
| sort @timestamp desc
| limit 20
```
- 結果

```
----------------------------------------------------------------------------------------------------------------------------------------------------------
|       @timestamp        |     @ingestionTime      | @logStream |        @message         |                 @log                  |         id          |
|-------------------------|-------------------------|------------|-------------------------|---------------------------------------|---------------------|
| 2021-11-24 14:09:30.882 | 2021-11-24 14:09:31.267 | pattern1   | {"id":3,"name":"zzzzz"} | 012345678901:/cloudwatch/insight/test | ":3,"name":"zzzzz"} |
| 2021-11-24 14:09:18.371 | 2021-11-24 14:09:18.895 | pattern1   | {"id":2,"name":"yyyyy"} | 012345678901:/cloudwatch/insight/test | ":2,"name":"yyyyy"} |
| 2021-11-24 14:08:58.528 | 2021-11-24 14:09:02.269 | pattern1   | {"id":1,"name":"xxxxx"} | 012345678901:/cloudwatch/insight/test | ":1,"name":"xxxxx"} |
----------------------------------------------------------------------------------------------------------------------------------------------------------
```

### 5.stats
#### count

- IDごとのカウントをとる。

```
fields @timestamp, @ingestionTime, @logStream, @message, @log
| parse @message  "\"id\":*," as parsed_id
| stats count(*) by parsed_id
| sort @parsed_id asc
| limit 20
```
- 結果

```
------------------------
| parsed_id | count(*) |
|-----------|----------|
| 3         | 1        |
| 2         | 1        |
| 1         | 1        |
------------------------
```


# 考察
- もっとJSONの抽出方法を調べる必要がある。

# 結論


# 参考
[サポートされるログと検出されるフィールド](https://docs.aws.amazon.com/ja_jp/AmazonCloudWatch/latest/logs/CWL_AnalyzeLogData-discoverable-fields.html#CWL_AnalyzeLogData-discoverable-JSON-logs)