User Profile
amolinamartinez
Copper Contributor
Joined Feb 10, 2021
User Widgets
Recent Discussions
Re: Kusto: Complex query for streaming IoT data
wernerzirkel 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.1.1KViews0likes0CommentsKusto: 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.Solved1.2KViews0likes2Comments
Recent Blog Articles
No content to show