Search code examples
kqlaskql

KQL group by or summarize count by see the rest of the columns

I would like to ask your help with a kql query:

DeviceEvents 
| distinct InitiatingProcessAccountUpn,DeviceName,DeviceId
| join DeviceTvmSoftwareVulnerabilities on $left.DeviceId == $right.DeviceId
| summarize count() by SoftwareName, DeviceId

The problem is I used summarize count by there are many software versions and I just want to see the version of the software from each machine, and it did the tick but I can not re-add the missing columns from the table.

I tried to rejoin with the same table but wasn't successfull.

What I would like to see is from one machine one software. Now, if I have this, I can see the name of the software and ID but I want to see in the final report the rest column as well.

Do you have any idea what I can do better? Thanks

Solution

  • you can use take_any:

    summarize take_any(SomeOtherColumns)

    Or you could add the other column as a grouping key and then do another summarize and aggregate it somehow (also take_any(), max(), arg_max(), make_list() etc)