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 locations (Warehouse1 and Warehouse2), each with a temperature and humidity sensor. In addition, there is a pushbutton, a button that when pressed sends a signal with a value "1".
I need to know the last value of temperature and humidity received at the time the button is pressed, differentiating by each location.
This would be an example of the dataset:
let iotdata = datatable(location:string, sensorName: string, value: real, timestamp:datetime)
[
"Warehouse1", "Temperature", 19.1,"2022-09-17 08:01:32",
"Warehouse1", "Temperature", 19.3,"2022-09-17 08:20:32",
"Warehouse1", "Temperature", 19.5,"2022-09-17 08:28:32",
"Warehouse1", "Temperature", 19.7,"2022-09-17 08:45:32",
"Warehouse1", "Temperature", 20.2,"2022-09-17 09:01:25",
"Warehouse1", "Temperature", 20.2,"2022-09-17 09:15:32",
"Warehouse1", "Temperature", 20.3,"2022-09-17 09:20:17",
"Warehouse1", "Temperature", 20.4,"2022-09-17 09:22:12",
"Warehouse1", "Temperature", 20.7,"2022-09-17 09:45:32",
"Warehouse1", "Temperature", 20.6,"2022-09-17 09:46:32",
"Warehouse1", "Temperature", 20.7,"2022-09-17 09:57:32",
"Warehouse1", "Temperature", 21.1,"2022-09-17 10:01:32",
"Warehouse1", "Temperature", 21.2,"2022-09-17 10:10:32",
"Warehouse1", "Humidity", 60,"2022-09-17 08:01:34",
"Warehouse1", "Humidity", 59,"2022-09-17 08:20:34",
"Warehouse1", "Humidity", 59,"2022-09-17 08:28:34",
"Warehouse1", "Humidity", 58,"2022-09-17 08:45:34",
"Warehouse1", "Humidity", 58,"2022-09-17 09:01:27",
"Warehouse1", "Humidity", 57,"2022-09-17 09:15:34",
"Warehouse1", "Humidity", 57,"2022-09-17 09:20:19",
"Warehouse1", "Humidity", 56,"2022-09-17 09:22:14",
"Warehouse1", "Humidity", 55,"2022-09-17 09:45:34",
"Warehouse1", "Humidity", 54,"2022-09-17 09:46:34",
"Warehouse1", "Humidity", 53,"2022-09-17 09:57:34",
"Warehouse1", "Humidity", 52,"2022-09-17 10:01:34",
"Warehouse1", "Humidity", 51,"2022-09-17 10:10:34",
"Warehouse1", "Button", 0,"2022-09-17 7:10:34",
"Warehouse1", "Button", 1,"2022-09-17 9:00:01",
"Warehouse1", "Button", 0,"2022-09-17 9:00:03",
"Warehouse1", "Button", 1,"2022-09-17 10:00:01",
"Warehouse2", "Temperature", 19.0,"2022-09-17 08:00:32",
"Warehouse2", "Temperature", 19.1,"2022-09-17 08:19:32",
"Warehouse2", "Temperature", 19.2,"2022-09-17 08:27:32",
"Warehouse2", "Temperature", 19.5,"2022-09-17 08:46:32",
"Warehouse2", "Temperature", 20.0,"2022-09-17 09:05:25",
"Warehouse2", "Temperature", 20.1,"2022-09-17 09:13:32",
"Warehouse2", "Temperature", 20.2,"2022-09-17 09:21:17",
"Warehouse2", "Temperature", 20.3,"2022-09-17 09:23:12",
"Warehouse2", "Temperature", 20.4,"2022-09-17 09:46:32",
"Warehouse2", "Temperature", 20.5,"2022-09-17 09:47:32",
"Warehouse2", "Temperature", 20.5,"2022-09-17 09:58:32",
"Warehouse2", "Temperature", 20.8,"2022-09-17 10:02:32",
"Warehouse2", "Temperature", 21.2,"2022-09-17 10:11:32",
"Warehouse2", "Humidity", 61,"2022-09-17 08:01:34",
"Warehouse2", "Humidity", 59,"2022-09-17 08:20:34",
"Warehouse2", "Humidity", 58,"2022-09-17 08:28:34",
"Warehouse2", "Humidity", 59,"2022-09-17 08:45:34",
"Warehouse2", "Humidity", 58,"2022-09-17 09:01:27",
"Warehouse2", "Humidity", 57,"2022-09-17 09:15:34",
"Warehouse2", "Humidity", 56,"2022-09-17 09:20:19",
"Warehouse2", "Humidity", 56,"2022-09-17 09:22:14",
"Warehouse2", "Humidity", 54,"2022-09-17 09:45:34",
"Warehouse2", "Humidity", 53,"2022-09-17 09:46:34",
"Warehouse2", "Humidity", 52,"2022-09-17 09:57:34",
"Warehouse2", "Humidity", 51,"2022-09-17 10:01:34",
"Warehouse2", "Humidity", 50,"2022-09-17 10:10:34",
"Warehouse2", "Button", 0,"2022-09-17 7:10:34",
"Warehouse2", "Button", 1,"2022-09-17 9:00:02",
"Warehouse2", "Button", 0,"2022-09-17 9:00:03",
"Warehouse2", "Button", 1,"2022-09-17 10:00:01",
]
Thank you in advance for whoever manages to solve this challenge.
- 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
2 Replies
- 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_sensorIt'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.
