Kusto Query Language（KQL）の join、union、サブクエリの例 (SQL の補足付き)

データ分析において、複数のテーブル間でデータを結合したり、複雑なクエリを実行する必要があります。
この記事では、Kusto Query Language (KQL) と SQL での join、union、サブクエリの例を紹介します。

join、union、サブクエリの例

KQL での join の使い方

KQL では、joinキーワードを使用し、kind = xxにて異なるテーブルの結合方法を指定します。
以下は、2 つのテーブルをjoinして結果を取得する例です。

SecurityEvent
| where EventID == "4624"
| summarize LogOnCount=count() by Account
| project LogOnCount, Account
// JOIN でアカウントごとのログオフ回数出して、結合させる
| join kind = inner (
    SecurityEvent
    | where EventID == "4634"
    | summarize LogOffCount=count() by Account
    | project LogOffCount, Account
    ) on Account

• 結合種類の詳細について参考記事
  • join 演算子
  • Microsoft Sentinel / Log Analytics で結合クエリー (JOIN) を使いこなす

SQL でもJOIN句を使用して異なるテーブルを結合します。
以下は、2 つのテーブルをJOINして結果を取得する例です。

SELECT
    s1.Account,
    s1.LogOnCount,
    s2.LogOffCount
FROM
    (SELECT
        Account,
        COUNT(*) AS LogOnCount
    FROM
        SecurityEvent
    WHERE
        EventID = '4624'
    GROUP BY
        Account) AS s1
JOIN
    (SELECT
        Account,
        COUNT(*) AS LogOffCount
    FROM
        SecurityEvent
    WHERE
        EventID = '4634'
    GROUP BY
        Account) AS s2
ON
    s1.Account = s2.Account

KQL での union の使い方

KQL の例

Table1
| project Column1, Column2;
| union
(
    Table2
    | project Column3, Column4;
)

// View1 を定義
let View1 =
    Table1
    | project Column1, Column2;

// View2 を定義
let View2 =
    Table2
    | project Column3, Column4;

// View1 と View2 を結合
View1 | union View2

SQL の例

-- クエリ1
SELECT Column1, Column2
FROM Table1

UNION

-- クエリ2
SELECT Column3, Column4
FROM Table2

入れ子になったクエリの例

KQL の例

SigninLogs
| where TimeGenerated > ago(1h) and ResultType == toscalar(
    SigninLogs
    | where TimeGenerated == '2024-04-05T07:32:54.567152Z'
    | project ResultType
)

SQL の例

SELECT *
FROM SigninLogs
WHERE TimeGenerated > DATEADD(HOUR, -1, GETUTCDATE())
AND ResultType = (
    SELECT ResultType
    FROM SigninLogs
    WHERE TimeGenerated = '2024-04-05 07:32:54.567152'
    AND CAST(TimeGenerated AS DATETIME) = '2024-04-05 07:32:54.567152'
)

参考記事

• join 演算子
• Microsoft Sentinel / Log Analytics で結合クエリー (JOIN) を使いこなす