Kusto: Complex query for streaming IoT data

Alberto Molina 6 Reputation points
2022-09-17T10:29:58.777+00:00

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.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
485 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Maxim Sergeev 6,566 Reputation points Microsoft Employee
    2022-09-19T17:03:49.923+00:00

    I assume arg_max() can help you here 

     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",  
 ];  
iotdata   
| summarize arg_max(timestamp, location, sensorName, value) by location, sensorName
    1 person found this answer helpful.
    0 comments
  2. Alberto Molina 6 Reputation points
    2022-09-23T12:28:16.933+00:00

    Hi Maxim,

    Your query returns something like this:

    244249-2022-09-23-14-23-40-kustoexplorer-v1031183.png

    But what is needed is the temperature and humidity value at the exact moment the button is pressed.

    Here, Werner Zirkel proposes a solution that solves the problem, in case it can help someone.

    Thanks for your help!

    1 person found this answer helpful.