More than 3 years have passed since last update.

Splunk: 表の結合方法あれこれ

Posted at 2022-01-23

しばしば必要となるのでメモ。

実施環境： Splunk Free 8.2.2

以下の2つの表を、様々な形式で結合してみます。

NUM1	STR1
1	a1
2	a2
3	a3

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| table NUM1, STR1

NUM2	STR2
3	b3
4	b4

Splunk

| makeresults count=2
| streamstats count AS NUM
| eval NUM2 = NUM + 2
| eval STR2 = "b" + NUM2
| table NUM2, STR2

縦に結合

単純に縦に繋げるには、 append コマンドを使用します。
無論、フィールド名は統一するようにしてください。

NUM1	STR1
1	a1
2	a2
3	a3
3	b3
4	b4

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| append
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1, STR2 AS STR1
    ]
| table NUM1, STR1

横に結合

横に繋げる場合は、 appendcols コマンドを使用します。

NUM1	STR1	NUM2	STR2
1	a1	3	b3
2	a2	4	b4
3	a3

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| appendcols
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
    ]
| table NUM1, STR1, NUM2, STR2

斜めに結合

フィールド名を統一せずに append コマンドを使用すると、以下のような形で結合されます。

NUM1	STR1	NUM2	STR2
1	a1
2	a2
3	a3
		3	b3
		4	b4

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| append
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
    ]
| table NUM1, STR1, NUM2, STR2

交差結合

全ての組み合わせを網羅する形での結合(交差結合)を実現したい場合は、 join コマンドを使用します。
join コマンドは通常メインサーチとサブサーチで指定したフィールドを比較して一致した行を結合しますが、フィールドを何も指定しない場合は単純にメインサーチ1行毎にサブサーチを結合していきます。
また、 join コマンドの max というパラメータはメインサーチ1行に対し結合できるサブサーチの行数の最大値を指定しており、デフォルトは1です。
これを0(無制限)にした上でフィールドを指定しなければ、以下のように交差結合を作ることができます。

NUM1	STR1	NUM2	STR2
1	a1	3	b3
1	a1	4	b4
2	a2	3	b3
2	a2	4	b4
3	a3	3	b3
3	a3	4	b4

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| join max=0
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
    ]
| table NUM1, STR1, NUM2, STR2

内部結合

上に書いた通り、 join コマンドは通常メインサーチとサブサーチで指定の項目が一致した行を結合する目的で使用されます。
デフォルトでは、 join コマンドはメインサーチとサブサーチの両方に指定の値が存在する行のみを抽出します(内部結合)。

NUM1	STR1	STR2
3	a3	b3

以下の例では、メインサーチの NUM1 とサブサーチの NUM2 を比較し、両方に存在する「3」の行のみを結合、抽出しています。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| join NUM1
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
    ]
| table NUM1, STR1, STR2

左外部結合

join コマンドの type パラメータに outer を指定した場合、メインサーチに含まれる値全てを出力します(左外部結合)。

NUM1	STR1	STR2
1	a1
2	a2
3	a3	b3

以下の例では、メインサーチとサブサーチ両方に存在する「3」だけでなく、メインサーチのみに存在する「1」及び「2」も出力されています。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| join type=outer NUM1
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
    ]
| table NUM1, STR1, STR2

右外部結合

サブサーチに含まれる値全てを出力する(右外部結合)ことは、 join コマンド単体では実現できません。

NUM1	STR1	STR2
3	a3	b3
4		b4

単純にメインサーチとサブサーチを入れ替えるのが最も簡単ですが、それが難しい場合は他のコマンドを合わせて使用するなどして上手く作るしかありません。
以下の例では元の表にNULL行を追加した上で一旦交差結合を作成して、メインサーチに存在しない値( NUM1 が NULL )の場合はサブサーチの値( NUM2 )をメインサーチの値に移し、メインサーチの値とサブサーチの値が等しくかつその値が NULL でない行を抽出した後、キーの値の重複を削除してヒットした値における NULL 行を削除しています。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| append
    [
      | makeresults count=1
    ]
| fields NUM1, STR1
| join max=0
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
    ]
| eval NUM1 = if(isnull(NUM1), NUM2, NUM1)
| where isnotnull(NUM1) AND (NUM1 = NUM2)
| table NUM1, STR1, STR2
| dedup NUM1

ただ、上記の例だとキーとなる値( NUM1 )に対してメインサーチの値( STR1 )とサブサーチの値( STR2 )が一意とならない場合、重複削除で必要な行も削られてしまい上手く動作しません。

性能は非常に悪化しますが、以下のように「メインサーチに含まれる部分(内部結合)」と「メインサーチに含まれない部分」を分割して処理する方法もあります。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| join NUM1
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
    ]
| append
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
      | where NOT (
          [
            | makeresults count=3
            | streamstats count AS NUM1
            | fields NUM1
            | format
          ]
              )
    ]
| table NUM1, STR1, STR2

こちらの例だとキーとなる値( NUM1 )に対してメインサーチの値( STR1 )とサブサーチの値( STR2 )が一意とならない場合も動作できます。

完全外部結合

メインサーチとサブサーチに含まれる値全てを出力する(完全外部結合)場合も、 join コマンド単体では作れません。

NUM1	STR1	STR2
1	a1
2	a2
3	a3	b3
4		b4

右外部結合と同様、他のコマンドと上手く組み合わせる必要があります。
以下の例は先程の右外部結合とよく似ていますが、メインサーチのみに存在する行が追加されています。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| append
    [
      | makeresults count=1
    ]
| fields NUM1, STR1
| join max=0
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | append
          [
            | makeresults count=1
          ]
      | fields NUM2, STR2
    ]
| eval NUM1 = if(isnull(NUM1), NUM2, NUM1)
| where isnotnull(NUM1) AND
        ((NUM1 = NUM2) OR isnull(NUM2))
| table NUM1, STR1, STR2
| dedup NUM1

より簡潔な書き方としては、以下のように stats コマンドの values 関数を使用する方法があります。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| append
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
    ]
| stats values(STR1) AS STR1,
        values(STR2) AS STR2 BY NUM1

上記2例は、キーとなる値( NUM1 )に対してメインサーチの値( STR1 )とサブサーチの値( STR2 )が一意となる場合に有効です。

右外部結合の際と同様、以下のように「メインサーチに含まれる部分(左外部結合)」と「メインサーチに含まれない部分」を分割して処理する方法もあります。

Splunk

| makeresults count=3
| streamstats count AS NUM1
| eval STR1 = "a" + NUM1
| fields NUM1, STR1
| join type=outer NUM1
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
    ]
| append
    [
      | makeresults count=2
      | streamstats count AS NUM
      | eval NUM2 = NUM + 2
      | eval STR2 = "b" + NUM2
      | fields NUM2, STR2
      | rename NUM2 AS NUM1
      | where NOT (
          [
            | makeresults count=3
            | streamstats count AS NUM1
            | fields NUM1
            | format
          ]
              )
    ]
| table NUM1, STR1, STR2

こちらの例だとキーとなる値( NUM1 )に対してメインサーチの値( STR1 )とサブサーチの値( STR2 )が一意とならない場合も動作できます。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up