[v9.0/v18.8] ES|QLでLOOKUP JOIN

# はじめに
Elasticsearch v9.0/v18.8からLOOKUP JOINが実装されました。待ち望まれていた方も多いのではないかと思います。
これまでもENRICHという方法もありましたが、ENRICHポリシーを作成する必要があり少し柔軟性にかけていました。
実装としてはいわゆるOUTER LEFT JOINです。
ただいくつか制限はあります。
- ES|QLでのみ利用
- LOOKUPモードでインデックスする
- v8.18/v9.0以降

:::note info
2025/04/16のv9.0.0を元に記事を記載しています
:::

# 手順
では実際に使ってみましょう。
JOINと言えばセキュリティ分野で使われることが多いと思います。

## Elasticsearchクラスタを準備
クラウドもしくはオンプレで用意します
- クラウド
    - https://qiita.com/tomo_s_el/items/3584d0b1fabb0bafa4fa
- オンプレ
    - https://qiita.com/takeo-furukubo/items/df17ca57203825a45da1

## 疑似ログ作成
こちらのGitリポジトリをcloneします。
https://github.com/legacyworld/lookup_join

生成AIにADとNGINXの疑似ログを作るプログラムを作らせました。便利な世の中になりました。
プログラムを見ると分かりますが、ADのログでは同じIPアドレスに複数のユーザが割り当てられることがあります（ランダムなので）
被らないようにも出来ますが、被ったときの挙動も見られるのでこのままにしておきます。

疑似ログの数とIPアドレスの範囲はソースコードにベタ書きしています。
READMEにあるように`.env`を作成しておきます。

`docker compose up -d`で立ち上げて`adlog.py`と`nginxlog.py`を実行します。
```
docker compose up -d
docker exec -it join python /src/adlog.py 
docker exec -it join python /src/nginxlog.py
```

AD Log
```
{'@timestamp': '2025-03-24T06:55:51', 'event_id': 4625, 'user.name': 'dunncurtis', 'event': 'User Logon', 'source.ip': '192.168.1.91'}
{'@timestamp': '2025-03-24T06:55:51', 'event_id': 4625, 'user.name': 'diane82', 'event': 'User Logon', 'source.ip': '192.168.1.166'}
{'@timestamp': '2025-03-24T06:45:51', 'event_id': 4624, 'user.name': 'wendycarter', 'event': 'User Logon', 'source.ip': '192.168.1.79'}
```

NGINX Log
```
"Mar 21, 2025 @ 17:51:20.000","xkTnt5UBqwkeZIyIP-bE","-","logs-nginx","-","/page-15.html","/page-15.html","HTTP/1.1","HTTP/1.1",DELETE,DELETE,"-","-","4,865","192.168.1.66",200,"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3"
"Mar 21, 2025 @ 17:44:33.000",BETnt5UBqwkeZIyIdOck,"-","logs-nginx","-","/page-20.html","/page-20.html","HTTP/1.1","HTTP/1.1",GET,GET,"-","-",929,"192.168.1.66",404,"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3"
```

### 疑似ログ確認

KibanaのDiscoveryで確認してみます。以下のドキュメントに沿ってData Viewを作成します。
https://www.elastic.co/guide/en/kibana/current/data-views.html

プログラムを実行した時間から1時間前まででランダムで作っているので、Time Rangeを`Last 1 hour`よりも範囲を広げておきます。
#### AD
200件入れてIPアドレス192.168.1.0 - 192.168.1.100の範囲にしています。
![スクリーンショット 2025-03-24 17.16.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2603229/2e8206d9-cdc5-4f54-85ee-130ff188509b.png)

#### NGINX
5000件入れてIPアドレス192.168.1.0 - 192.168.1.100の範囲にしているので、`source.ip`も適度に分布しています。
![スクリーンショット 2025-03-24 17.11.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2603229/4bb681b6-90af-421a-a963-8688db8b294a.png)

#### lookupモード
実はAD Log(Lookupされる方)はインデックスのモードをlookupモードにしています。
ソースコードでは以下の部分で指定しています。
```python
def create_index():
    """インデックスを作成する"""
    body = {
    "settings": {
      "index": {
        "mode": "lookup"
      }
    },
        "mappings": {
            "properties": {
            "source.ip": {
                "type": "ip"
            }
            }
        }
    }
    es.indices.create(index=INDEX_NAME, body=body)
```

# ES|QL
LOOKUP JOINはES|QLのみ対応なので、Discovery画面で`Try ES|QL`をクリックします。
まずは以下のように入れてみます。
```
FROM logs-ad
| STATS COUNT_DISTINCT(source.ip)
```
IPアドレスが何種類ぐらい作られたか見てみます。
![スクリーンショット 2025-03-26 16.46.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2603229/6cdc8eaa-a8fb-455b-9c54-55f1aca39b4a.png)

87なのでいくつか同じIPアドレスで違うユーザネームで作成されているはずです（ユーザネームはFakeで作成している）
ランダムなのでこの値はいろいろ変わります

## LOOKUP JOIN
では本記事の主題であるLOOKUP JOINを行ってみます。キーは`source.ip`です。`@timestamp`は同じ名前なので変更しておきます
```
FROM logs-nginx
| RENAME @timestamp AS timestamp_nginx
| LOOKUP JOIN logs-ad ON source.ip
| KEEP timestamp_nginx,source.ip,user.name
```
![スクリーンショット 2025-03-26 17.44.16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2603229/ce329e4c-6071-4455-9a70-8a595026ecfd.png)
ランダムなので結果は色々変わりますが、`user.name`がない場合は`-`になっていますし、一つのIPアドレスで複数ユーザログインがある場合は直積(Direct Product)されています。
上図のキャプチャだと`192.168.1.45`が直積されているのがわかります。

# まとめ
まだ制限はありますが、LOOKUP JOINができることで特にセキュリティ分野においてかなり使いやすくなったのではないかと思います。