【SC-200】KQLクエリの書き方 – extract編

2023/6/24 Microsoft Defender, Sentinel

皆さんこんにちは。国井です。
前回紹介したKQLクエリの書き方シリーズの第10弾として extract 関数を紹介します。

正規表現を利用した文字列の整形

extract 関数は正規表現を利用した文字列の整形に利用します。
例えばユーザー名でドメイン名\ユーザー名みたいな感じで入っているときにユーザー名だけを取り出したいってときありますよね。
そのような場合にはこんな感じで書いてあげます。

extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))

tolower(Account)と、Account列を小文字にするという余計な処理もいれてありますが、これでユーザー名部分だけを取り出すことができます。一方、extract 関数は以前に紹介した extend 演算子と共に使えば別の列を作って結果を表示させることができます。

SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))

実行結果はこちら。Account_Name列がユーザー名だけを取り出した列になります。

image

サインイン回数の多いユーザー Top3 を取り出す

ここから先は extract 関数など全然関係ないのですがユースケースをひとつ。
この結果から Summarize を使えばユーザーごとのサインイン回数をカウントできます。

SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| summarize Attempts = count() by Account_Name

image

さらにこの結果から | top 3 by 列名 と書いてあげると列名で指定した数が最も多いもの Top 3 (サインイン回数Top3) だけを出力することができます。

SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| summarize Attempts = count() by Account_Name
| where Account_Name != ""
| top 3 by Attempts

image

Top3 とその他を表示する

Top3で登場したユーザーはそのサインイン回数がそのまま表示されますが、「その他」のユーザーはTop3以外のすべてのユーザーによるサインインを数えて結果を表示する処理が必要になります。言葉にするとわかりにくいですが、つまりこういうことです。
Otherってところにすべてのユーザーの合計サインイン回数が書いてあります。

image

まず最初に前に登場したTop3を表示するクエリをmake_listを使ってリスト化しておきます。

SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| summarize Attempts = count() by Account_Name
| where Account_Name != ""
| top 3 by Attempts 
| summarize make_list(Account_Name)

さらに let を使って関数にしてしまうと後で使い勝手がよくなります。
(ここではtop3関数と定義しておきました)

let top3 = SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| summarize Attempts = count() by Account_Name
| where Account_Name != ""
| top 3 by Attempts 
| summarize make_list(Account_Name);

そして、関数定義のあとにもう一度 SecurityEvent から書き始めます。

let top3 = SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Account_Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| summarize Attempts = count() by Account_Name
| where Account_Name != ""
| top 3 by Attempts 
| summarize make_list(Account_Name);
SecurityEvent
| where TimeGenerated > ago(1h)
| where AccountType == 'User'
| extend Name = extract(@"^(.*\\)?([^@]*)(@.*)?$", 2, tolower(Account))
| extend Account_Name = iff(Name in (top3), Name, "Other")
| where Account_Name != ""
| summarize Attempts = count() by Account_Name
| sort by Attempts

image

これでTop3のユーザーとそれ以外のユーザーのサインイン回数が表示されたことがわかります。それ以外のユーザーのサインイン回数部分をどうやって処理したかを見てみましょう。

まず後半のSecurityEventから| extend Name = extract(@”^(.*\\)?([^@]*)(@.*)?$”, 2, tolower(Account))までは前と同じことをしていることがわかります。
注目したいのはその次の行で

| extend Account_Name = iff(Name in (top3), Name, "Other")

このように書くとtop3以外のユーザーはOtherというカテゴリに収納されます。
意味としては Account_Name 列に書かれたユーザーが top3 のユーザーだったらという条件をiff関数を使って条件分岐をしています。
iff関数は

iff(条件, 正の場合, 偽の場合)

この書き方をします。そのため、iff(Name in (top3), Name, “Other”) と書けばtop3関数の実行結果に登場したユーザーであれば、その名前をそのまま利用し、それ以外のユーザーであればOtherに収納します。このようにユーザーを分類した後で、| summarize count() by Account_Name と書けば、それぞれのユーザーのサインイン関数を数えます。top3のユーザーは各ユーザーのサインイン回数、Otherのユーザーはすべてのユーザーのサインイン回数の合計を数えます。

■ ■ ■

2023年6月24日追記
extractのユースケースとして、1つの列の中に複数の列と値が入っている場合、特定の列内の値だけ取り出したい場合があります。
具体的に言うと、以下のケースだとDeviceDetail列内にbroser, deviceid.. などの列があるけど、broser列の値だけを取り出して表示したいというケースです。

image

この場合、

| SigninLogs
| extend Browser=extract('"browser":"(.*)"',1,tolower(DeviceDetail))

のように書いてあげます。そうすると新しくBrowser列ができて元々のbrowser列に記載されていた値が入ることがわかります。

image

■ ■ ■

今回は extract 関数そのものの利用方法をみてもらいたかったので、正規表現そのものの説明であったり、iff関数の説明だったりをちょっと端折って説明してしまいました。どこかの機会で改めて解説できればと思います。

Suguru KUNII