KQL / Kusto query in ADX to Extend Table A with calculated value based on a subquery for each row

Question

My source data is something like this:

timestamp       ObjectID    Sensor A    Sensor B    Sensor C
3/18/2022 9:40         1           5           1           1
3/18/2022 9:41         1          15           1           2
3/18/2022 9:42         1          25           1           3
3/18/2022 9:43         1          35           1           4
3/18/2022 9:44         1          45           1           5
3/18/2022 9:45         1          55           1           6
3/18/2022 9:46         1          65           1           7
3/18/2022 9:47         1          75           1           6
3/18/2022 9:48         1          85           4           6
3/18/2022 9:49         1          95           4           7
3/18/2022 9:50         1         105           4           8
3/18/2022 9:51         1          33           4           9
3/18/2022 9:52         1          43           1          10
3/18/2022 9:53         1          53           1          14
3/18/2022 9:54         1          63           1          13
3/18/2022 9:55         1          73           1          13
3/18/2022 9:56         1          83           4          12
3/18/2022 9:57         1          93           4          13

I want to get to this:

ObjectID    Start           End             Max_Sensor_C
       1    3/18/2022 9:45  3/18/2022 9:48             7
       1    3/18/2022 9:53  3/18/2022 9:56            14

I have a table of timeseries data in Azure Data Explorer (ADX) that contains multiple sensors. I apply a rule to Sensor "A" I find a "startTime" and a different rule to Sensor "B" I find an end Time. Then I filter to get a new table that is just: Key, startTime, endTime

Pseudo Code:

myDataTable
| project myRowKey, SensorA, SensorB, timestamp
| order by timestamp asc
//These two lines change based on what the start and end trigger are
| extend startTrigger = iff(SensorA > 50, true, false)
| extend endTrigger = iff(SensorB == 4, true, false)
//Reduce the table to just start/end pairs
| where startTrigger==true and prev(startTrigger)==false or endTrigger==true and prev(endTrigger)==false
| extend end=iff(endTrigger==true and prev(startTrigger)==true, timestamp, datetime(null))
| extend start=iff(endTrigger==true and prev(startTrigger)==true, prev(timestamp), datetime(null))
//Reduce the table to just one line with both start/end columns
| where end != datetime(null) and start != datetime(null)
| project myRowKey, end, start

I have the above working, but now I am stuck. I want to extend each row with a timeseries statistic about Sensor "C" calculated over the time period between start and end. Max value for example. I think the query itself would look something like this:

myDataTable
| where myRowKey == Value AND timestamp between(start .. end)
| summarize maxSensorC = max(SensorC), myRowKey, start 

If I knew what I was doing (which I don't), I would iterate over each row of the first table and extend it with the value calculated from the second query. Any help with pointing me in the right direction would be appreciated.

Answer 1

using pack() and toscalar(), you can build a property bag that includes your 3 different parameters, then reference those later.

For example:

let params = toscalar(T
    ...
    | project pack("myRowKey", myRowKey, "start", start, "end", end)
);
OtherT
| where timestamp between(todatetime(params.start) .. todatetime(params.end))
| where Value == tostring(params.myRowKey)
...