[Cortex XDR/XSIAM]2つのクエリ結果を結合する - JoinとUnion

[massaito]

この記事では、2つのXQLクエリの結果を結合するコマンドJoinとUnionについて紹介します。

Cortex XDRはCortex XDR Pro per Endopointで収集したEDRのデータや、

NGFWファイアウォールログ、クラウドなどサードパーティログを取り込むことができますが、

JoinやUnionコマンドを使うことで、これらのログのデータ横断的な調査・分析、

BIOC、Correlation Ruleなどを用いた脅威検出にご利用いただくことができます。

Join、Union共に、クエリ結果を結合するコマンドであることには変わりはないのですが、

結合の考え方が異なりますので、簡単に説明します。

Joinとは

JoinコマンドはSQLのJoinと似た概念のコマンドで、Inner join, Right join, Left joinといった結合方法が用意されています。

2つのクエリの結果セットを結合する条件を設定し、列で結合します。

例えば、以下のような2つの結果セット(result A,result B)があったとします。

result A

#	host_id	host_name
1	01	server01
2	02	server02

result B

#	id	admin_name
1	01	admin_a
2	02	admin_b

result AのHOST_IDとresult BのIDを条件一致のキーすることによって、

以下のように結合されます。(列に追加)

#	host_id	id	host_name	admin_name
1	01	01	server01	admin_a
2	02	02	server02	admin_b

xdr_dataデータセットと、endpointsデータセットをjoinで結合する

以下のようなXQLを考えてみます。

dataset=xdr_data
| dedup actor_process_image_name ,agent_hostname, agent_id
| top 3 agent_id
| join type = left
(
dataset=endpoints
| dedup endpoint_name, endpoint_id
| fields endpoint_id, endpoint_name, ip_address , tags , endpoint_isolated , endpoint_type
)
as ep ep.endpoint_id= agent_id

前半のクエリ

dataset=xdr_data
| dedup actor_process_image_name ,agent_hostname, agent_id
| top 3 agent_id

では、agent_idで集計したTOP3のAgent_IDが返ってきます。

後半のjoinコマンドで実行するクエリ、

dataset=endpoints
| dedup endpoint_name, endpoint_id
| fields endpoint_id, endpoint_name, ip_address , tags , endpoint_isolated , endpoint_type

では、

以下のような結果が返ってきます。

as ep ep.endpoint_id= agent_idでは、endpoint_idとagent_idを条件一致のキーとしており、これによって以下の結果が返ってきます。

緑の枠が前半のクエリの実行結果を示しており、水色の枠が後半のクエリの実行結果を示しています。

またjoin typeをleft joinとしてるため、前半のクエリ結果(3件)と、条件に一致する後半のクエリ結果が結合した状態で取得されます。

Unionとは

unionコマンドは、2つのクエリの結果セットを行で結合します。

例えば、以下のような２つの異なる結果セットがあった場合、

result A

#1	servername	host_ip
1	Server01	xx.xx.xx.xx
2	Server02	yy.yy.yy.yy

result B

#	mobile_name	host_ip
1	iPhone A	zz.zz.zz.zz
2	Android B	uu.uu.uu.uu

以下のように結合するイメージです。(行に追加)

#	servername	mobile_name	host_ip
1	Server01		xx.xx.xx.xx
2	Server02		yy.yy.yy.yy
3		iPhone A	zz.zz.zz.zz
4		Android B	uu.uu.uu.uu

結合する際にフィールド名を一致させるようXQLで変換しておく、

例えばservernameとmobile_nameをhost_nameに変換すると、

以下のような結果が返ります。

#	host_name	host_ip
1	Server01	xx.xx.xx.xx
2	Server02	yy.yy.yy.yy
3	iPhone A	zz.zz.zz.zz
4	Android B	uu.uu.uu.uu

xdr_dataデータセットと、ngfwデータセットをunionで結合する

以下のようなXQLを考えてみます。

エンドポイントデータのネットワーク通信ログとNGFWのトラフィックログを結合します。

dataset=xdr_data
  | filter action_remote_ip != null and agent_hostname != null and actor_process_image_name != null
  | fields actor_process_image_name ,agent_hostname, action_remote_ip
  | limit 5
  | union
    (

      dataset=panw_ngfw_traffic_raw
        | fields from_zone ,dest_ip , source_ip
        | limit 5
    )

まず、前半のクエリの結果を確認します。

dataset=xdr_data
  | filter action_remote_ip != null and agent_hostname != null and actor_process_image_name != null
  | fields actor_process_image_name ,agent_hostname, action_remote_ip
  | limit 5

プロセスイメージ名、エージェントホスト名、通信先のIPアドレスが5件結果として返ってきました。

後半のクエリの結果を確認します。

dataset=panw_ngfw_traffic_raw
| fields from_zone ,dest_ip , source_ip
| limit 5

ゾーン、宛先IP、ソースIPが5件結果として返ってきました。

上記2つのクエリをunionで結合すると、以下のように行で結合していることがわかります。

水色の枠がエンドポイントデータのネットワーク通信ログ、緑色の枠がNGFWのトラフィックログを示しています。

ただし宛先IPアドレスが別々のフィールド名として結合するため、使い勝手があまり良くありません。

そこで元のXQLの前半のクエリでaction_remote_ipフィールドをdest_ipに変換しておきます。

変換する場合は、<元のフィールド名 as 任意のフィールド名>　という形で指定します。

dataset=xdr_data
  | filter action_remote_ip != null and agent_hostname != null and actor_process_image_name != null
  | fields actor_process_image_name ,agent_hostname, action_remote_ip as dest_ip
  | limit 5
  | union
    (

      dataset=panw_ngfw_traffic_raw
      | fields from_zone ,dest_ip , source_ip
      | limit 5
    )

そうすると、以下のようにフィールド名とそのデータが統合され結果が返ってきました。

このように、複数のデータセットを結合して、データを検索したい場合、同じ意味を持つフィールド名を統一しておくと、

検索の効率性が大きく向上します。

Tech Doc

joinに関する詳細はこちら

unionに関する詳細はこちら