Query Real-Time IoT Data in Azure Stream Analytics

Note: The point of this lab is to write queries and not to test your ability to get the sample data loaded. If you have any trouble, at this point, go ahead and check the lab guide or the solution video.

• Reasonable column names would be: time (no alias), SensorName, Temperature, and Humidity.

• Correctly written, your query should return 389 rows in the Test results tab below the query pane.

Building on your first query in the last objective, add a column called Status that returns the string Alert if the temperature is above 100 and the humidity is below 40. If it's not, it should return OK in the status column.

Hints:

• This query uses a CASE statement.
• This query should return the same 389 rows as the first query challenge, but with an additional column that includes an alert when the described temperature and humidity conditions are present.

Suppose you want to get the average temperature, every 1 minute, over all sensors. You want to display 2 columns: the timestamp representing the minute and the average temperature for that minute. Write a query that fulfills these requirements.

Hints:

• Apply the appropriate aggregation to the temp column, and use the tumbling window function set to group and report every 1 minute.
• Use the TIMESTAMP BY time clause to use the time field from the sample data as the event timestamp.
• This query should return 32 rows with average temperatures between 101 and 114 (rounded).
• Because streaming data queries are typically interested in understanding the data output in timeframes, a good majority of the stream analytics queries will include one of a few windowing functions. See the Additional Resources section of this lab for a links to Azure Stream Analytics query documentation.

In casual observation, you note that the sample data is a slice of data from a single day, and the messages are sent at inconsistent intervals. You want to get a better sense of the shape of the data by using overlapping intervals of time to smooth out anomalies, so you write a query to check every 1 minute for the count of messages sent over the last 5 minutes. You decide to limit your query to data from Sensor E.

Write a query to return 2 columns: the timestamp representing the interval/event timestamp and a column with the count of events over the last 5 minutes.

Hints:

• Use a hopping window function to "hop" each minute, with a window size of 5 minutes.
• Use the TIMESTAMP BY time clause to use the time field from the sample data as the event timestamp.
• This query should return 36 rows, with timestamps 1 minute apart, and the count of messages sent in the previous 5 minutes.
• Because streaming data queries are typically interested in understanding the data output in timeframes, a good majority of the stream analytics queries will include one of a few windowing functions. See the Additional Resources section of this lab for a links to Azure Stream Analytics query documentation.