how to parse JSON number Object dynamically in KUSTO

Question

I am trying to parse fields under "list" object which are nested numbered JSON object . these list number object is dynamic created (from 1 to 10) . Can somebody help writing query in KQL to flatten the "list" object and retrieve the data under this. eg: machineinfo.list.0.name // machineinfo.list.[0-2].name get all names from this number object

"machineinfo": {
    "last": {
        "error": "0",
        "netadapter": {
            "count": "3",
            "list": {
                "0": {
                    "name": "Intel(R) Wi-Fi 6E AX211 160MHz",
                    "macaddress": "11:11:11:11:11:11:test"
                },
                "1": {
                    "name": "Microsoft Wi-Fi Direct Virtual Adapter",
                    "macaddress": "00:00:00:00:00:test"
                },
                "2": {
                    "name": "Bluetooth Device (Personal Area Network)",
                    "macaddress": "22:22:22:22:22:test"
                }
            }
        },
        "bios": {
            "version": "test - 1111",
            "manufacturer": "Test Inc.",
            "sn": "test"
        },
        "motherboard": {
            "manufacturer": "test",
            "sn": "testvsdfa",
            "product": "test123"
        }
    }
}

RemoteAccess | take 100 | project MyJSON = todynamic(machineinfo.['last'].['netadapter'].['list']) | project MyJSONFirst= MyJSON["0"] | extend name1= tostring(MyJSONFirst.name)

Answer 1

you could try using the mv-apply operator.

for example:

let RemoteAccess  = datatable(row_number:long, payload: dynamic)
[
    1, dynamic({"machineinfo": {
    "last": {
    "error": "0",
    "netadapter": {
    "count": "3",
    "list": {
    "0": {
    "name": "Intel(R) Wi-Fi 6E AX211 160MHz",
    "macaddress": "11:11:11:11:11:11:test"
    },
    "1": {
    "name": "Microsoft Wi-Fi Direct Virtual Adapter",
    "macaddress": "00:00:00:00:00:test"
    },
    "2": {
    "name": "Bluetooth Device (Personal Area Network)",
    "macaddress": "22:22:22:22:22:test"
    }
    }
    },
    "bios": {
    "version": "test - 1111",
    "manufacturer": "Test Inc.",
    "sn": "test"
    },
    "motherboard": {
    "manufacturer": "test",
    "sn": "testvsdfa",
    "product": "test123"
    }
    }
    }})
]
;
RemoteAccess
| mv-apply element = payload['machineinfo']['last']['netadapter']['list'] on (
    extend key = tostring(bag_keys(element)[0])
    | project name = element[key].name
    | summarize names = make_list(name)
)
| project row_number, names

row_number	names
1	[ "Intel(R) Wi-Fi 6E AX211 160MHz", "Microsoft Wi-Fi Direct Virtual Adapter", "Bluetooth Device (Personal Area Network)" ]

Answer 2

Another operator that could solve your question (each name in a different row, so depending on how the result should look like), is mv-expand.

The KQL looks as follows:

let raw_data = datatable (data : dynamic)
[
dynamic ({
"machineinfo": { 
    "last": {
        "error": "0",
        "netadapter": {
            "count": "3",
            "list": {
                "0": {
                    "name": "Intel(R) Wi-Fi 6E AX211 160MHz",
                    "macaddress": "11:11:11:11:11:11:test"
                },
                "1": {
                    "name": "Microsoft Wi-Fi Direct Virtual Adapter",
                    "macaddress": "00:00:00:00:00:test"
                },
                "2": {
                    "name": "Bluetooth Device (Personal Area Network)",
                    "macaddress": "22:22:22:22:22:test"
                }
            }
        },
        "bios": {
            "version": "test - 1111",
            "manufacturer": "Test Inc.",
            "sn": "test"
        },
        "motherboard": {
            "manufacturer": "test",
            "sn": "testvsdfa",
            "product": "test123"
        }
    }
}}
)
];
raw_data
| mv-expand kind=array netapplist=data.machineinfo['last'].netadapter.list
| project number=netapplist[0], name= netapplist[1].name