TrafficPeak: Hydrolixに格納されたAkamaiのログをSQLで取得する

# はじめに

Akamaiのエッジプラットフォームのログを収集するためには、DataStream2というソリューションで、指定したプラットフォームにリアルタイムにログをストリーミングします。
また、WAFのログはSIEMインテグレーションというしくみを利用して、ログを外部で収集できます。
TrafficPeakはそれらのログを蓄積し、可視化できる基盤を備えているソリューションです。
今回は、TrafficPeakに蓄積されたログをSQLにて取得する方法を紹介します。

## TrafficPeak とは

TrafficPeakとは、Akamaiが提供するログ可視化ソリューションです。
DataStream2の宛先としてTrafficPeakを指定することにより、ログの蓄積と可視化を低コストで可能にします。
このソリューションはAkamai Cloud Computing Services上で提供されるマネージドサービスとなり、コンピューティングパートナーであるHydrolix社がサポートします。

https://qiita.com/isss802/items/8d4f1b48c4d1000617a0


## Hydrolix とは

Hydrolixは高可用性に優れ、ペタバイトのスケールが可能な、時系列データベースプラットフォームを提供しています。
追記専用のデータベースとして特化しており、バックエンドにオブジェクトストレージを利用することで大容量のデータでも長期間低価格で保存することができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/49877/19257933-0705-292d-4fe2-37e705526ccb.png)



## SQLを実行する方法

TrafficPeakはCDNなどのログをHydrolixのプラットフォームに格納し、照会などのために用いるSQLインターフェイスを備えています。

SQLにてアクセスするには以下の方法で接続することができます。

- API
- Native Interface (Clickhouse Client / JDBC Drivers / Python Libraries)
- Hydrolix UI

https://docs.hydrolix.io/docs/query-interfaces

今回は Hydrolix UIとClickhouse Clientの接続について紹介します。

## Hydrolix UI

WebブラウザからHydrolixのUIにアクセスしクエリを実行することができます。
このWebインターフェイスはバックエンドでAPIを呼び出します。
簡易的なSQLの実行には向いていますが、10,000行以上のクエリは実行できない制限があります。
そのため、データのエクスポートなどを行う際には他の方法でSQLを実行する必要があります。

※一部プランではUIの提供を行っておりません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/49877/4b4a4d9d-03a9-5c8f-d782-9d7827890136.png)

## Clickhouse Client

HydrolixはClickhouseのSQLエンジンを利用しており、Clickhouse ClientからSQL を利用できます。

今回はmacOSにClickhouse CLIをインストールし、SQLを実行する手順を紹介します。

https://clickhouse.com/docs/en/install

brewにてCLIをインストールします。

```
brew install clickhouse/clickhouse/clickhouse
```

正常にインストールできているか確認をします。

```
clickhouse client --version
ClickHouse client version 23.5.3.1.
```

Clickhouse CLIを利用してHydrolixのプラットフォームに接続します。
hostname、user、passwordは環境に合わせて修正する必要があります。

```
clickhouse client -s -h xxx.trafficpeak.live --port 9440 --user "$user" --password "$password"
```

接続が完了するとSQLが実施できるようになります。
テストでデータベースの一覧を確認し、作成したテナントの名前があるかどうか確認をします。

```sql
query-peer :) SHOW DATABASES;


SHOW DATABASES

Query id: 

┌─name───────────┐
│ <Tenant_Name>  │
│ _local         │
│ akamai         │
│ hdx            │
│ hydro          │
│ sample_project │
│ system         │
└────────────────┘
```

TrafficPeakはデフォルトで３つのテーブルを用意し対象のログが格納されます。

:::note warn
専有インスタンスではakamaiのデータベースに保存されています。
:::

```sql
query-peer :) SHOW TABLES FROM <Tenant_Name>;


SHOW TABLES FROM <Tenant_Name>

Query id:

┌─name──┐
│ logs  │ CDNのログが格納されているテーブル
│ media │ CMCDのログが格納されているテーブル
│ siem  │ WAFのログが格納されているテーブル
└───────┘
```

### DataStream2のログをSQLにて取得

まずはlogsテーブルのCDNログを確認してみます。

直近１時間のログを取得します。（時刻はUTCです）
尚、表示は１行に絞っています。

```sql
query-peer :) SELECT *
FROM <Tenant_Name>.logs
WHERE reqTimeSec > (now() - toIntervalHour(1))
LIMIT 1
```

問題なくログが表示されたのを確認し、ログをファイルに格納します。
直近1時間のログをjsonファイルにエクスポートします。

```sql
query-peer :) SELECT *
FROM <Tenant_Name>.logs
WHERE reqTimeSec > (now() - toIntervalHour(1))
INTO OUTFILE 'logs.json'
```

取得したjsonファイルの内容を確認し、期待している値が格納されている確認をします。

```json
cat logs.json | jq '.data[0]'
{
  "CloudWrapper_ASN": null,
  "CloudWrapper_DNSLookupTime": null,
  "CloudWrapper_GeoInfo": null,
  "CloudWrapper_IP": null,
  "CloudWrapper_RequestEndTime": null,
  "CloudWrapper_RequestID": null,
  "CloudWrapper_TurnAroundTime": null,
  "Edge_ASN": null,
  "Edge_DNSLookupTime": null,
  "Edge_GeoInfo": null,
  "Edge_IP": null,
  "Edge_RequestEndTime": null,
  "Edge_RequestID": null,
  "Edge_TurnAroundTime": null,
  "Origin_ASN": null,
  "Origin_DNSLookupTime": null,
  "Origin_GeoInfo": null,
  "Origin_IP": null,
  "Origin_RequestEndTime": null,
  "Origin_RequestID": null,
  "Origin_TurnAroundTime": null,
  "Parent_ASN": null,
  "Parent_DNSLookupTime": null,
  "Parent_GeoInfo": null,
  "Parent_IP": null,
  "Parent_RequestEndTime": null,
  "Parent_RequestID": null,
  "Parent_TurnAroundTime": null,
  "Peer_ASN": null,
  "Peer_DNSLookupTime": null,
  "Peer_GeoInfo": null,
  "Peer_IP": null,
  "Peer_RequestEndTime": null,
  "Peer_RequestID": null,
  "Peer_TurnAroundTime": null,
  "UA": "xxxxxxxxx/0.0.1",
  "accLang": null,
  "asn": "xxxxx",
  "billingRegion": 5,
  "breadcrumbs": "",
  "brotliStatus": null,
  "bytes": "123",
  "cacheStatus": 0,
  "cacheable": 0,
  "city": "TOKYO",
  "cliIP": "xxx.xxx.xxx.xxx",
  "cmcd": null,
  "cmcd_buffer_length": null,
  "cmcd_buffer_starvation": 0,
  "cmcd_content_id": null,
  "cmcd_deadline": null,
  "cmcd_encoded_bitrate": null,
  "cmcd_measured_throughput": null,
  "cmcd_next_object_requests": null,
  "cmcd_next_range_request": null,
  "cmcd_object_duration": null,
  "cmcd_object_type": null,
  "cmcd_playback_duration": null,
  "cmcd_playback_rate": null,
  "cmcd_requested_max_throughput": null,
  "cmcd_session_id": null,
  "cmcd_startup": 0,
  "cmcd_stream_type": null,
  "cmcd_streaming_format": null,
  "cmcd_top_bitrate": null,
  "cmcd_version": null,
  "cookie": null,
  "country": "JP",
  "cp": xxxxxx,
  "customField": null,
  "dnsLookupTimeMSec": null,
  "edgeAttempts": 1,
  "edgeIP": null,
  "errorCode": null,
  "ewExecutionInfo": null,
  "ewUsageInfo": null,
  "fileSizeBucket": "0-1KB",
  "isMidgress": null,
  "lastByte": 0,
  "latency": 106,
  "maxAgeSec": null,
  "objSize": "123",
  "originContentLen": null,
  "overheadBytes": "928",
  "proto": "HTTPS/1.1",
  "queryStr": "",
  "range": null,
  "referer": "",
  "reqEndTimeMSec": 11,
  "reqHost": "www.example.com",
  "reqId": "xxxxxx",
  "reqMethod": "GET",
  "reqPath": "/XXXXXXXXXX/List/XXXXXXXXXX",
  "reqPort": 443,
  "reqTimeSec": "2023-10-13 08:03:31.674",
  "rspContentLen": "123",
  "rspContentType": "text/html",
  "securityRules": "xxxxxxx|xxxxxxxxxxx",
  "serverCountry": "JP",
  "state": "Tokyo-to",
  "statusCode": 302,
  "streamId": xxxxxx,
  "timeToFirstByte": xxxxxx,
  "tlsOverheadTimeMSec": 6,
  "tlsVersion": "TLSv1.3",
  "totalBytes": "1111",
  "transferTimeMSec": 1,
  "turnAroundTimeMSec": 11111,
  "uncompressedSize": null,
  "unknown": {
    "contentProtectionInfo": "-"
  },
  "version": 1,
  "xForwardedFor": null
}
```

### SIEMのログをSQLにて取得

SIEMのログをSQLでエクスポートします。
SIEMの場合、時間のカラムはtimestampになります。（時刻はUTCです）
直近１時間のログをjsonファイルにエクスポートします。

```sql
query-peer :) SELECT *
FROM <Tenant_Name>.siem
WHERE timestamp > (now() - toIntervalHour(1))
INTO OUTFILE 'siem-1.json'
```

```json
cat siem-1.json | jq '.data[0]'
{
  "asn": "xxxx",
  "botData": {},
  "bytes": "7888",
  "city": "OSAKA",
  "clientData": {},
  "clientIP": "xxx.xxx.xxx.xxx",
  "configId": "xxxx",
  "continent": "AS",
  "country": "JP",
  "format": "json",
  "host": "www.example.com",
  "method": "POST",
  "path": "/xxx/xxx/xxx/xxxxx",
  "policyId": "xxxxxx",
  "port": 443,
  "protocol": "HTTP/1.1",
  "regionCode": "27",
  "requestHeaders": {
    "Host": "www.example.com",
    "Accept": "text/html,application/xhtml+xml,application/xml;",
    "Sec-Fetch-Site": "same-origin",
    "Accept-Language": "ja",
    "Accept-Encoding": "gzip, deflate, br",
    "Sec-Fetch-Mode": "navigate",
    "Content-Type": "application/x-www-form-urlencoded",
    "Origin": "https",
    "User-Agent": "xxxxx",
    "Referer": "https",
    "Content-Length": "1098",
    "Connection": "keep-alive",
    "Sec-Fetch-Dest": "document",
    "Cookie": xxxxxx=xxxxxx"
  },
  "requestHeadersStr": null,
  "requestId": "xxxxxxx",
  "responseHeaders": {
    "Content-Length": "7888",
    "Content-Type": "text/html; charset=utf-8",
    "Access-Control-Expose-Headers": "Request-Context",
    "Content-Encoding": "gzip",
    "Vary": "Accept-Encoding",
    "X-AspNet-Version": "x.x.xxxxx",
    "Request-Context": "appId=cid-v1",
    "Expires": "Sat, 14 Oct 2023 07",
    "Cache-Control": "max-age=0, no-cache, no-store",
    "Date": "Sat, 14 Oct 2023 07",
    "Connection": "keep-alive",
    "Set-Cookie": "xxxxxxx; domain=example.com; expires=Fri, 22-Sep-2113 16"
  },
  "responseHeadersStr": null,
  "ruleActions": [
    "allow"
  ],
  "ruleActionsStr": null,
  "ruleData": [
    "xxx"
  ],
  "ruleDataStr": null,
  "ruleMessages": [
    "xxx"
  ],
  "ruleMessagesStr": null,
  "ruleSelectors": [],
  "ruleSelectorsStr": null,
  "ruleTags": [
    "AKAMAI/BOT/CUST_DEFINED_BOTS"
  ],
  "ruleTagsStr": null,
  "ruleVersions": [],
  "ruleVersionsStr": null,
  "rules": [
    "BOT-xxxxxxxxxf"
  ],
  "rulesStr": null,
  "status": 200,
  "timestamp": "2023-10-14 07:15:00",
  "tls": "tls1.3",
  "tor": "2023-10-14 07:15:13",
  "type": "akamai_siem",
  "userRiskData": {},
  "version": "1.0"
}
```

### クエリのTips

WHERE句を修正することで特定のログを取得することができるようになります。

指定した日時以降のログを取得する
（SIEMの場合はreqTimeSecをtimestampにする）

```sql
WHERE reqTimeSec >= '2023-10-12 22:00:00'
```

指定した日時内のログを取得する (and)

```sql
WHERE reqTimeSec >= '2023-10-12 22:00:00' and  timestamp <= '2023-10-12 23:00:00'
```

指定した日時内のログを取得する (between)

```sql
WHERE reqTimeSec between '2023-10-12 22:00:00' and '2023-10-12-23:00:00'
```

# まとめ

TrafficPeakを利用することで大量のCDNログなどを保存したHydrolixの基盤からSQLを利用して必要な分のログを抽出、確認できるようになります。
トラブルシュートの際に迅速な対応をとる場合などに利用していただければと思います。


# 関連記事

アカマイ・テクノロジーズ合同会社はQiitaでAkamai Cloud Computing Services関連など開発者向けの記事を掲載しております。

https://qiita.com/xenbase/items/52e2956a64cedc772bd9