Sentinel KQL JSON with Dynamic Label

Question

I'm experimenting with Microsoft Sentinel and trying to understand how to parse JSON elements. One experiment is that I've wired my house with temperature and humidity sensors and fed them in, now the difficulty is the parsing... they're syslog events with a Message containing JSON as shown below.

SENSOR = 
{
    "ZbReceived":
    {
        "0x03FA":
        {
            "Device":"0x03FA",
            "Name":"2_Back_Bedroom",
            "Humidity":71.66,"Endpoint":1,
            "LinkQuality":66
        }
    }
}

Unfortunately the devices include the device ID as a label in the JSON, which makes it hard for me to figure out how to extract all the fields. There are 8 sensors, so repeating this for every one of them seems inefficient, but maybe it's necessary?

Is there a way I could extract the values from 8 different sensors? I've tried .[0]. and other variants, but no luck.

print T = dynamic('SENSOR = {"ZbReceived":{"0x03FA":{"Device":"0x03FA","Name":"2_Back_Bedroom","Humidity":71.66,"Endpoint":1,"LinkQuality":66}}}')
| mv-expand humidity = parse_json(substring(T, 9)).ZbReceived.["0x03FA"].Humidity
| mv-expand device = parse_json(substring(T, 9)).ZbReceived.["0x03FA"].Device
| mv-expand name = parse_json(substring(T, 9)).ZbReceived.["0x03FA"].Name
| mv-expand battery = parse_json(substring(T, 9)).ZbReceived.["0x03FA"].Battery
| mv-expand temperature = parse_json(substring(T, 9)).ZbReceived.["0x03FA"].Temperature

What's the point of storing the data as dynamic if it's not a valid JSON? Do you really need the "SENSOR = " part? — David דודו Markovitz, Oct 11 '22 at 13:56
@DavidדודוMarkovitz it's part of the syslog message. I'm leaving "SENSOR =" in because the solution for the problem will have to parse it out. It's very possible I'm misusing the dynamic keyword. — mgjk, Oct 11 '22 at 15:11

David דודו Markovitz · Accepted Answer · 2022-10-12T07:02:32.687

Quick explanation:
Under

print T = dynamic('SENSOR = {"ZbReceived":{"0x03FA":{"Device":"0x03FA","Name":"2_Back_Bedroom","Humidity":71.66,"Endpoint":1,"LinkQuality":66}}}')
| parse tostring(T) with  "SENSOR = " sensor:dynamic
| project device = sensor.ZbReceived[tostring(bag_keys(sensor.ZbReceived)[0])]
| evaluate bag_unpack(device)

Device	Endpoint	Humidity	LinkQuality	Name
0x03FA	1	71.66	66	2_Back_Bedroom

Fiddle

P.S.
For clarity, the line with the project operator could be replaced with the following 2 lines:

| extend device_id = tostring(bag_keys(sensor.ZbReceived)[0]) // e.g., 0x03FA
| project device = sensor.ZbReceived[device_id]

hmm... I'm going to have to read a little to understand why this works. It's excellent, thank you. — mgjk, Oct 11 '22 at 15:07

Sentinel KQL JSON with Dynamic Label

1 Answers1