KQL update table query data issues

Silver Contributor

Oct 23, 2023

Hi RutterA,

To achieve this in KQL, you can use the "union" operator. Here's how you can try to modify your queries to get the desired output:

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 | project Computer, Month="August", Status=Aug_Installed),
    (SEPTUPDATES | project Computer, Month="September", Status=Sep_Installed),
    (OCTUPDATES | project Computer, Month="October", Status=Oct_Installed)
| summarize Status=make_set(Status) by Computer, Month
| project Computer, Month, IsInstalled=iff(array_length(Status) > 0, "Installed", "Not Installed")
| order by Computer, Month

The first three lines create three tables, one for each month, that contain the list of VMs that have the specified KB update installed.
The next line uses the union operator to combine the three tables into a single table.
The summarize statement groups the data by computer and month and creates a set of statuses for each computer and month.
The project statement checks if there are any installed statuses for each month and creates an IsInstalled column accordingly.
The final line orders the results by computer and month.

This query will produce a table with the following columns:

Computer: The name of the VM.
Month: The month of the KB update.
IsInstalled: A value of Installed or Not Installed, depending on whether the VM has the KB update installed.

Here is an example of the output from the query:

Computer | Month | IsInstalled
------- | ----- | -----------
VM1     | August | Installed
VM2     | August | Installed
VM3     | August | Not Installed
VM1     | September | Installed
VM2     | September | Not Installed
VM3     | September | Installed
VM1     | October | Installed
VM2     | October | Installed
VM3     | October | Not Installed

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

Forum Discussion

Resources