Search code examples
azure-data-explorerkql

KQL make_set or make_list on rows into columns

I have a basic query in Defender

DeviceInfo
| join DeviceNetworkInfo on DeviceId
| project DeviceId,NetworkAdapterType,IPAddresses

The result is a lot of repeating rows, I want to move this --- enter image description here

To something like this -- enter image description here

Basically, I wanted to see if could "distinct" a column and roll up other columns into lists or arrays using make_set or make_list? I wasn't sure if this was possible... thanks!!!

Solution

  • I have reproduced in my environment and got expected results as below:

    Below is the example kql query which worked for me:

    datatable(ID:int, DEPT:string, ADDRESS:dynamic)
[
    8, "CSE", dynamic([1.2, "Rithwik"]),
    8, "CSE", dynamic([1.3, "hello"]),
    8, "ECE", dynamic([1.4, "Bojja"])
]
| summarize dep = make_set(DEPT), ipadress = make_list(ADDRESS) by ID

    Output:

    enter image description here

    enter image description here

    Fiddle.

    For your query you can integrate from summarize statement.

    DeviceInfo
| join DeviceNetworkInfo on DeviceId
| project DeviceId,NetworkAdapterType,IPAddresses
| summarize dep = make_set(NetworkAdapterType), ipadress = make_list(IPAddresses) by DeviceId