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

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

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

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

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

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

## 実例

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

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

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

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

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

```PowerShell
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 の曜日番号を追加する。

```PowerShell
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文字目を切り出して曜日番号とみなすことにする。

```PowerShell
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 可能になる。

```PowerShell
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
```

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

```PowerShell
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) を取得する方法がわかれば、クエリ結果の日付部分に曜日を追加するのは比較的簡単にできることがわかった。