Forum Discussion
Solved
Kusto: Complex query for streaming IoT data
Hi, I am wasting hours trying to solve a query in Kusto, I think this requires the expertise of some expert. I have a simplified IoT dataset for the example. Suppose there are two physical locat...
- amolinamartinez
this code searches for the temperature and humidty value that is nearest to the button pressed event (per warehouse). So the outcome is one humidy and one temperature value per button clicked event. Is that right ? Then this code should do it.
I am separating the tables for buttons and humidity/temperature first of all.
Then I join them and compare the timestamps of the button and the sensor value ("timediff_button_sensor").
let iotdata...; ++ //add your table, dontwant top copy it again
let button_pressed =
iotdata
| where sensorName == "Button" and value == 1
| project location, timestamp;
let sensors =
iotdata
| where sensorName == "Humidity" or sensorName =="Temperature"; //only humidity and temp
button_pressed
| join kind=leftouter
sensors on location
| extend timediff_button_sensor = timestamp - timestamp1
| where timediff_button_sensor >= 0s //the button time must be greater than the sensor time
| summarize min(timediff_button_sensor), timestamp = max(timestamp1) by location, button_time = timestamp, sensorName //for each button pressed, get the latest hum and temp sensor by warehouse
| join kind=inner iotdata on timestamp, sensorName, location
| project location, button_time, sensorName, sensor_time = timestamp, value, min_timediff_button_sensor
amolinamartinez
this code searches for the temperature and humidty value that is nearest to the button pressed event (per warehouse). So the outcome is one humidy and one temperature value per button clicked event. Is that right ? Then this code should do it.
I am separating the tables for buttons and humidity/temperature first of all.
Then I join them and compare the timestamps of the button and the sensor value ("timediff_button_sensor").
let iotdata...; ++ //add your table, dontwant top copy it again
let button_pressed =
iotdata
| where sensorName == "Button" and value == 1
| project location, timestamp;
let sensors =
iotdata
| where sensorName == "Humidity" or sensorName =="Temperature"; //only humidity and temp
button_pressed
| join kind=leftouter
sensors on location
| extend timediff_button_sensor = timestamp - timestamp1
| where timediff_button_sensor >= 0s //the button time must be greater than the sensor time
| summarize min(timediff_button_sensor), timestamp = max(timestamp1) by location, button_time = timestamp, sensorName //for each button pressed, get the latest hum and temp sensor by warehouse
| join kind=inner iotdata on timestamp, sensorName, location
| project location, button_time, sensorName, sensor_time = timestamp, value, min_timediff_button_sensor
this code searches for the temperature and humidty value that is nearest to the button pressed event (per warehouse). So the outcome is one humidy and one temperature value per button clicked event. Is that right ? Then this code should do it.
I am separating the tables for buttons and humidity/temperature first of all.
Then I join them and compare the timestamps of the button and the sensor value ("timediff_button_sensor").
let iotdata...; ++ //add your table, dontwant top copy it again
let button_pressed =
iotdata
| where sensorName == "Button" and value == 1
| project location, timestamp;
let sensors =
iotdata
| where sensorName == "Humidity" or sensorName =="Temperature"; //only humidity and temp
button_pressed
| join kind=leftouter
sensors on location
| extend timediff_button_sensor = timestamp - timestamp1
| where timediff_button_sensor >= 0s //the button time must be greater than the sensor time
| summarize min(timediff_button_sensor), timestamp = max(timestamp1) by location, button_time = timestamp, sensorName //for each button pressed, get the latest hum and temp sensor by warehouse
| join kind=inner iotdata on timestamp, sensorName, location
| project location, button_time, sensorName, sensor_time = timestamp, value, min_timediff_button_sensor
It's amazing how simple is the query you've made. The query that I tried to do was long and did not solve the problem.
Thanks for the time spent Werner!
I added the pivot function at the end to get the result of all the sensors in a single row:
let button_pressed = iotdata | where sensorName == "Button" and value == 1 | project location, timestamp; let sensors = iotdata | where sensorName == "Humidity" or sensorName =="Temperature"; //only humidity and temp let results = button_pressed | join kind=leftouter sensors on location | extend timediff_button_sensor = timestamp - timestamp1 | where timediff_button_sensor >= 0s //the button time must be greater than the sensor time | summarize min(timediff_button_sensor), timestamp = max(timestamp1) by location, button_time = timestamp, sensorName //for each button pressed, get the latest hum and temp sensor by warehouse | join kind=inner iotdata on timestamp, sensorName, location | project location, button_time, sensorName, sensor_time = timestamp, value, min_timediff_button_sensor; results | evaluate pivot(sensorName, max(value), location, button_time)Thanks for your help.
