如何用KQL查询生成VM月度KB更新安装状态列表示例

我想要生成一个展示VM及月度KB更新安装状态的表格，但遇到了问题：

• 使用Inner Join查询时，结果只返回了所有KB都已安装的VM，漏掉了那些部分安装或未安装的VM
• 使用Union查询时，无法将同一VM的各月状态合并到一行，达不到预期的表格格式

以下是我尝试的两次查询代码：

尝试查询1（Inner Join） #

let AUGUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5029242"
    | project Computer, Aug_Installed=UpdateState;
let SEPTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5030213"
    | project Computer, Sep_Installed=UpdateState;
let OCTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5031362"
    | project Computer, Oct_Installed=UpdateState;
AUGUPDATES
| join kind=inner (SEPTUPDATES) on Computer
| join kind=inner (OCTUPDATES) on Computer

（注：原代码中AUGTUPDATES是笔误，应为AUGUPDATES）

尝试查询2（Union） #

//UNION
let AUGUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5029242"
    | project Computer, Aug_Installed=UpdateState;
let SEPTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5030213"
    | project Computer, Sep_Installed=UpdateState;
let OCTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5031362"
    | project Computer, Oct_Installed=UpdateState;
union AUGUPDATES, SEPTUPDATES, OCTUPDATES

问题原因 #

1. Inner Join的问题：Inner Join要求关联的所有表中都存在同一Computer的记录才会返回，所以只会显示三个KB都已安装的VM，完全体现不了未安装的情况。
2. Union的问题：Union只是把三个表的数据纵向拼在一起，同一VM的不同月份状态会分成多行显示，根本没法实现一行展示所有月份状态的需求。

正确的查询方案 #

方案1：用pivot实现（推荐）

这种方式直接把月份转成列，效率更高，代码更简洁：

Update
| where KBID in ("5029242", "5030213", "5031362")
| extend Month = case(
    KBID == "5029242", "Aug",
    KBID == "5030213", "Sep",
    KBID == "5031362", "Oct",
    "Unknown"
)
| summarize Installed_State = max(UpdateState) by Computer, Month
| pivot Month, any(Installed_State)
| extend Aug_Installed = coalesce(Aug, "Not Installed"),
         Sep_Installed = coalesce(Sep, "Not Installed"),
         Oct_Installed = coalesce(Oct, "Not Installed")
| project Computer, Aug_Installed, Sep_Installed, Oct_Installed

方案2：用Left Join保留所有VM

先获取所有VM的列表，再通过Left Join关联每个月的更新记录，确保所有VM都能被展示：

let ALL_VMS = Update | distinct Computer; // 先拿到所有VM的列表
let AUGUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5029242"
    | project Computer, Aug_Installed=UpdateState;
let SEPTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5030213"
    | project Computer, Sep_Installed=UpdateState;
let OCTUPDATES =
    Update
    | where UpdateState == "Installed"
    | where KBID == "5031362"
    | project Computer, Oct_Installed=UpdateState;
ALL_VMS
| join kind=leftouter (AUGUPDATES) on Computer
| join kind=leftouter (SEPTUPDATES) on Computer
| join kind=leftouter (OCTUPDATES) on Computer
| extend Aug_Installed = coalesce(Aug_Installed, "Not Installed"),
         Sep_Installed = coalesce(Sep_Installed, "Not Installed"),
         Oct_Installed = coalesce(Oct_Installed, "Not Installed")
| project Computer, Aug_Installed, Sep_Installed, Oct_Installed

说明 #

• 方案1里的pivot会自动把Month列的不同值转成新列，coalesce用来把空值（也就是该VM没安装对应KB的情况）替换成"Not Installed"。
• 方案2的Left Join会保留所有VM的记录，不管有没有安装对应KB，同样用coalesce填充未安装的状态。

内容的提问来源于stack exchange，提问作者CodePug