Forum Discussion

RutterA's avatar
RutterA
Copper Contributor
Oct 21, 2023

KQL update table query data issues

I've been trying and failing/falling down a rabbit hole trying to output a table showing vms and monthly KBs install status as columns. I've tried both Join and Union but in the case of Join I just get all as installed and when I use Union I don't see the expected data.

https://i.stack.imgur.com/mz1BM.jpg

Attempted queries

//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

;

AUGTUPDATES | join kind=inner (SEPTUPDATES) on Computer | join kind=inner (OCTUPDATES) on Computer

--------And----

//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

azure
KQL
servicing and updates
updates

2 Replies

  • LeonPavesic's avatar
    LeonPavesic
    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)

    • Adrian_Rutter's avatar
      Adrian_Rutter
      Copper Contributor
      Oct 23, 2023

      LeonPavesic - thanks for the help (couldn't get the required syntax) that gives me the data so that I can transform it in excel

Resources