KQLの日付列に曜日を追加する方法

はじめに

KQLで日別の集計データなどの日付列に曜日を追加したい場合のクエリの書き方を実例で説明する。

利用する関数、ステートメント

datatable

曜日番号と曜日名の対比テーブル(ハッシュテーブル)を定義するために利用する。

dayofweek

日付型データから曜日番号を取得するために利用する。

join

曜日番号と曜日名の対比テーブルと、日付型データから取得した曜日番号を紐づける(JOINする)ために利用する。

実例

曜日番号と曜日名の対比テーブルの定義

datatable ステートメントを利用して曜日番号と曜日名の対比テーブルを定義する。
データ部は2次元ではなく、キーと値をそのまま並べて記述すればよい。

let WeekDays = datatable(DayNum:string, DayName:string) [
    "0", "日", "1", "月", "2", "火", "3", "水", "4", "木", "5", "金", "6", "土"
];

Defender for Endpoint の DeviveEvents テーブルでの利用例

例として特定デバイスの日別のログ件数を集計する。

DeviceEvents
| where TimeGenerated >= ago(7d)
| where DeviceName == "device001"
| summarize count() by bin( TimeGenerated, 1d )
| project TimeGenerated, count_

このクエリ結果は以下のようになっているはず。

TimeGenerated             count_
2025/04/07 00:00:00.000   12345
2025/04/04 00:00:00.000   54321

上記のクエリに dayofweek関数で TimeGenerated の曜日番号を追加する。

DeviceEvents
| where TimeGenerated >= ago(7d)
| where DeviceName == "device001"
| summarize count() by bin( TimeGenerated, 1d )
| extend DayOfWeek = dayofweek(TimeGenerated)
| project TimeGenerated, DayOfWeek, count_

このクエリ結果は以下のようになっているはず。

TimeGenerated             DayOfWeek      count_
2025/04/07 00:00:00.000   1.00:00:00     12345
2025/04/04 00:00:00.000   5.00:00:00     54321

この状態の DayOfWeek は日曜日が "00:00:00"、金曜日が "5.00:00:00" といった値になっているため1文字目を切り出して曜日番号とみなすことにする。

DeviceEvents
| where TimeGenerated >= ago(7d)
| where DeviceName == "device001"
| summarize count() by bin( TimeGenerated, 1d )
| extend DayNum = substring(dayofweek(TimeGenerated), 0, 1)
| project TimeGenerated, DayNum, count_

このクエリ結果は以下のようになっているはず。

TimeGenerated             DayNum    count_
2025/04/07 00:00:00.000   1         12345
2025/04/04 00:00:00.000   5         54321

ここまでで DayNum の値は日曜日が "0"、金曜日が "5" などとなっているため、上記の曜日番号と曜日名の対比テーブルと join 可能になる。

let WeekDays = datatable(DayNum:string, DayName:string) [
    "0", "日", "1", "月", "2", "火", "3", "水", "4", "木", "5", "金", "6", "土"
];
DeviceEvents
| where TimeGenerated >= ago(7d)
| where DeviceName == "device001"
| summarize count() by bin( TimeGenerated, 1d )
| extend DayNum = substring(dayofweek(TimeGenerated), 0, 1)
| join kind=leftouter WeekDays on DayNum
| project TimeGenerated, DayName, count_

このクエリ結果は以下のようになっているはず。

TimeGenerated             DayName    count_
2025/04/07 00:00:00.000   月         12345
2025/04/04 00:00:00.000   金         54321

あとは必要に応じて日時部分を文字列に整形すればよい

let WeekDays = datatable(DayNum:string, DayName:string) [
    "0", "日", "1", "月", "2", "火", "3", "水", "4", "木", "5", "金", "6", "土"
];
DeviceEvents
| where TimeGenerated >= ago(7d)
| where DeviceName == "device001"
| summarize count() by bin( TimeGenerated, 1d )
| extend DayNum = substring(dayofweek(TimeGenerated), 0, 1)
| join kind=leftouter WeekDays on DayNum
| extend Date = strcat(format_datetime(TimeGenerated, 'yyyy/MM/dd'), ' (', DayName, ')')
| project Date, count_
| order by Date desc

このクエリ結果は以下のようになっているはず。

Date              count_
2025/04/07 (月)   12345
2025/04/04 (金)   54321

まとめ

KQLでハッシュテーブルを定義する方法、日付型から曜日に対応する数値 (0～6) を取得する方法がわかれば、クエリ結果の日付部分に曜日を追加するのは比較的簡単にできることがわかった。