Sumo Logic 検索術 〜 こんな時どうやってクエリを書けばいいの？

今回は、Sumo Logic での検索クエリの使い方にフォーカスを当てて、ケース別にクエリの作り方をご紹介したいと思います。 （今回取り上げるクエリ例については、あらかじめフィールド化（パース）されている前提でご紹介していきます。パースについては、パースオペレーターかFER等を使って行って下さい。）

わたしの個人的な意見ですが、SIEM での分析のほとんどはログ内のデータを何かの観点でカウントすることで、状態の変化を読み取ったり、必要なデータをハイライトさせることができます。

以下のようなログが Sumo Logic に取り込まれている前提で見ていきます。

ログのソースカテゴリ:
_sourceCategory = furusatoApp/products

ログデータ:

レコード数をカウントしたい時

冒頭でも申し上げた通り、分析の基本となります。

出力された行数を数えるには以下のようにします。

クエリ

_sourceCategory = furusatoApp/products
| count

結果

グルーピングしてカウントしたい時は以下のようにします。

クエリ

_sourceCategory = furusatoApp/products
| count by %"品名"

結果

検索結果を特定のフィールドのみに絞って表示させたい（ダッシュボード表示）

検索結果を特定のフィールドに限定したい時は以下のようにします。

クエリ

_sourceCategory = furusatoApp/products
| fields %"時間", %"品名"

結果

※また、fields には表示順を並べる効果もあります。

特定のフィールドだけを省きたい時は以下のようにします。

クエリ

_sourceCategory = furusatoApp/products
| fields - %"卸先"

結果

ただし、検索結果に対して単に fields を使った場合、パネル表示ができません（ダッシュボードにするにはパネル表示する必要があります）。パネルは必ず集計関数（count など）を利用する必要があります。
そういった時は最終的に表示させたいフィールド＋α（ユニークな行が出力されるような）のグルーピングでカウントします。タイムスタンプをグルーピング条件に加えるとほとんどのケースでユニークな表示が可能です。

クエリ

_sourceCategory = furusatoApp/products
| count by %"時間", %"種別", %"品名"

結果

集計関数を使った後であれば fields で表示項目を絞ってパネル表示することができます。

クエリ

_sourceCategory = furusatoApp/products
| count by %"時間", %"種別", %"品名"
| fields - _count

結果

時系列データを作成したい時

折れ線グラフなどを使って時系列データを作りたい時に、ログを特定の時間で区切って、その期間内に出現した行数をカウントします。
時系列データをつくるには以下のように timeslice と count を組み合わせます。

_sourceCategory = furusatoApp/products
| timeslice 15m
| count by _timeslice

結果

棒グラフや折れ線グラフを選択すると、時系列変化を見ることができます。

検索範囲や出力されるログの量によって、区切る時間を調整します。
検索範囲が広い場合は、ある程度長い時間で区切ったり、ログの出力頻度が多く、細かくトラッキングしたい場合は区切る時間を短くして検索範囲もせまくします。

列名としての表示を変えたい時（列名を追加したい時）

列名としての表示を変えたい時は、以下のようにすることでフィールド名を新しく作ることができます。

_sourceCategory = furusatoApp/products
| %"品名" as %"商品名"

結果

フィールドに値を入れたい時は以下のようにします。

_sourceCategory = furusatoApp/products
| "100" as %"値段"

結果

条件に合う個数を求めたい時

条件で絞り込む場合、where を使います。
条件で絞り込んだ後に、件数をカウントすることで条件に合う個数を出すことが可能です。

_sourceCategory = furusatoApp/products
| where %"品名" = "りんご"
| count

結果

条件に合う個数を2つ以上求めた後に計算したい時

条件に合う個数を2つ以上求めた後、その結果を計算したい場合などはどうしたらよいでしょう。
例えば、野菜は全体の種別の何割を占めているかを計算したいとします。
条件ということで、where で絞り計算をしていこうと思うと、以下のようなクエリを考えてみます。

_sourceCategory = furusatoApp/products
| where !isEmpty(%"種別")
| count as %"種別総数"
| where %"種別" = "野菜"
| count  as %"野菜総数"

すると、下記のようにフィールドが見つからずエラーとなってしまいます。

理由としては、最初の count 関数の後では、「種別総数」フィールドのみで、「種別」のフィールドは解釈できなくなっているためです。

_sourceCategory = furusatoApp/products
| where !isEmpty(%"種別")
| count as %"種別総数"

結果

こういう時、先程あった列の追加を応用し、if と組み合わせます。
種別が野菜であれば野菜数に1、種別が何らか入力されていれば種別数に1を入れます。

_sourceCategory = furusatoApp/products
| if(%"種別" matches "野菜", 1, 0) as %"野菜数"
| if(!isEmpty(%"種別"), 1, 0) as %"種別数"

結果

さらに total（フィールド内の数値合計を出す演算子）を使って野菜数、種別数の集計を出すことができます。

_sourceCategory = furusatoApp/products
| if(%"種別" matches "野菜", 1, 0) as %"野菜数"
| if(!isEmpty(%"種別"), 1, 0) as %"種別数"
| total %"野菜数" as %"野菜総数"
| total %"種別数" as %"種別総数"
| fields %"時間", %"種別", %"野菜総数", %"種別総数"

結果

数値同士の足し算など計算したい

先程の集計値の値同士を足し算してみます。

_sourceCategory = furusatoApp/products
| if(%"種別" matches "野菜", 1, 0) as %"野菜数"
| if(!isEmpty(%"種別"), 1, 0) as %"種別数"
| total %"野菜数" as %"野菜総数"
| total %"種別数" as %"種別総数"
| %"野菜総数" / %"種別総数" as %"野菜割合"
| fields %"時間",%"種別", %"野菜総数", %"種別総数", %"野菜割合"

結果

条件に応じて値段を入れて、値段の合計を求める

先程と同じですが、もう一例やってみます。
品名がレモンならレモン値段に80、りんごならりんご値段に100、ねぎならネギ値段に250を入れます。

_sourceCategory = furusatoApp/products
| if(%"品名" matches "レモン", 80, 0) as %"レモン値段"
| if(%"品名" matches "りんご", 100, 0) as %"りんご値段"
| if(%"品名" matches "ねぎ", 250, 0) as %"ねぎ値段"

結果

さらに total（フィールド内の数値合計を出す演算子）を使ってそれぞれの値段の集計を出すことができます。

_sourceCategory = furusatoApp/products
| if(%"品名" matches "レモン", 80, 0) as %"レモン値段"
| if(%"品名" matches "りんご", 100, 0) as %"りんご値段"
| if(%"品名" matches "ねぎ", 250, 0) as %"ねぎ値段"
| total %"レモン値段" as %"レモン集計"
| total %"りんご値段" as %"りんご集計"
| total %"ねぎ値段" as %"ねぎ集計"
| fields %"時間", %"品名", %"レモン集計", %"りんご集計", %"ねぎ集計"

結果

さらに集計値の値同士を足し算してみます。

_sourceCategory = furusatoApp/products
| if(%"品名" matches "レモン", 80, 0) as %"レモン値段"
| if(%"品名" matches "りんご", 100, 0) as %"りんご値段"
| if(%"品名" matches "ねぎ", 250, 0) as %"ねぎ値段"
| total %"レモン値段" as %"レモン集計"
| total %"りんご値段" as %"りんご集計"
| total %"ねぎ値段" as %"ねぎ集計"
| %"レモン集計" + %"りんご集計" + %"ねぎ集計" as %"合計額"
| fields %"時間", %"品名", %"レモン集計", %"りんご集計", %"ねぎ集計", %"合計額"

結果

値表示の時に単位をつけたい

表示する時に「%」とか「¥」などの単位をつけたい時があります。

_sourceCategory = furusatoApp/products
| if(%"品名" matches "レモン", 80, 0) as %"レモン値段"
| if(%"品名" matches "りんご", 100, 0) as %"りんご値段"
| if(%"品名" matches "ねぎ", 250, 0) as %"ねぎ値段"
| total %"レモン値段" as %"レモン集計"
| total %"りんご値段" as %"りんご集計"
| total %"ねぎ値段" as %"ねぎ集計"
| %"レモン集計" + %"りんご集計" + %"ねぎ集計" as %"合計額"
| concat("¥", %"合計額") as %"合計額"
| fields %"時間", %"品名", %"合計額"

結果

重複を削除したい場合

フィールド内のユニークな値を取り出したい時、dedup を使います。

_sourceCategory = furusatoApp/products
| dedup by %"品名"

結果

重複を除いて、データの個数を求めたい時

この例の場合だと、品名の数を数えたい時に利用します。

_sourceCategory = furusatoApp/products
| dedup by %"品名"
| count

結果

まとめ

Sumo Logic でこんな分析ってどうしたらいいんだろうと思った時に、具体的な例を用いて紹介してみました。
少しくせがあると思うのですが、パターン的には今回紹介した方法を応用するとおおまかなケースでヒントになるのではないかと思います。
またよくありそうな分析観点とクエリの書き方が思い付けば書こうと思います。