let systems = datatable(traceName: string, trace: string)
[
'Temp1', 'trace_1_temperature',
'Temp2', 'trace_2_temperature',
'Temp3', 'trace_3_temperature'
];
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage':5, 'active':'true'}),
datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage':5, 'active':'true'}),
datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage':5, 'active':'true'})
];
My desired output hardcoded for this case:
let hardCodedDesiredOutput = telemetry
| extend Temp1 = data['trace_1_temperature']
| extend Temp2 = data['trace_2_temperature']
| extend Temp3 = data['trace_3_temperature']
| project-keep timestamp, Temp1, Temp2, Temp3;
hardCodedDesiredOutput
Output:
timestamp Temp1 Temp2 Temp3
2023-09-11 10:00:00.0000000 35 40
2023-09-11 10:05:00.0000000 36 42
2023-09-11 10:10:00.0000000 38 41
As you can see above, we have extracted a subset of properties from the Dynamic data column. Note that the systems table is structured similar to the output, s.t. columns are named based on an associated friendly name. I desire a more dynamic approach to extending the columns without explicit naming.
I’ve ran into this in the past, where we may have N many dynamic properties per telemetry row entry.I have tried using bag_unpack with outputSchema, however I’m still struggling to reconcile pulling in another table such as Systems to relate the data.
It feels like there should be a more straightforward programmatic way to do this in KQL, perhaps I’m missing something obvious. Is it possible to bag_unpack while filtering out certain properties conditionally, e.g. for each properties, only unpack iff property.key startswith “VAL”.
// Desired structure:
// let dynamicExtend = telemetry
// | based on traces listed in systems, extend a column for each listed trace
// Alternatively some sort of extract-from-dynamic based on if the key property startswith/endswith a value e.i. "temperature"
possible solution with mv-apply and make_bag:
let systems = datatable(traceName: string, trace: string)
[
'Temp1', 'trace_1_temperature',
'Temp2', 'trace_2_temperature',
'Temp3', 'trace_3_temperature'
];
// create dictionary from systems
let dictionary = toscalar(
systems
| where trace endswith "temperature" // filter if needed
| summarize make_bag(bag_pack(trace, traceName))
);
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage': 5, 'active': 'true'}),
datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage': 5, 'active': 'true'}),
datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage': 5, 'active': 'true'})
];
telemetry
| mv-apply data on (
mv-expand kind=array data
| extend key = tostring(data[0])
| extend key2 = tostring(dictionary[key])
| where isnotempty(key2) // filter data
| extend data = bag_pack(key2, data[1]) // rename key
| summarize data = make_bag(data)
)
| evaluate bag_unpack(data)
Try with the below approach, it uses mv-expand and make-bag to dynamically retrieves telemetry data from the systems table. The bag_unpack method separates the resulting bag into individual columns.
let systems = datatable(traceName: string, trace: string)
[
'Temp1', 'trace_1_temperature',
'Temp2', 'trace_2_temperature',
'Temp3', 'trace_3_temperature'
];
let telemetry = datatable(timestamp: datetime, data: dynamic)
[
datetime(2023-09-11T10:00:00Z), dynamic({'trace_1_temperature': 35, 'trace_2_temperature': 40, 'voltage':5, 'active':'true'}),
datetime(2023-09-11T10:05:00Z), dynamic({'trace_1_temperature': 36, 'trace_3_temperature': 42, 'voltage':5, 'active':'true'}),
datetime(2023-09-11T10:10:00Z), dynamic({'trace_2_temperature': 38, 'trace_3_temperature': 41, 'voltage':5, 'active':'true'})
];
let extractedTelemetry = telemetry
| extend data = todynamic(data)
| mv-expand kvp = bag_keys(data)
| extend kvp = tostring(kvp)
| extend value = data[kvp]
| project timestamp, kvp, value;
let mappedTelemetry = extractedTelemetry
| join kind=inner (systems) on $left.kvp == $right.trace
| project timestamp, traceName, value;
let dynamicOutput = mappedTelemetry
| summarize make_bag(pack(traceName, value)) by timestamp
| evaluate bag_unpack(bag_);
dynamicOutput
Output: