Azure Stream Analytics Windowing

Question

I'm having some trouble with Stream Analytics Windowing. The input to my Stream Analytics Job is an event stream that contains temperature readings from sensors/entities which belong to a signal. Every signal has about 600 entities. A sample event would look like this.

{
    "SignalId": "1",
    "EntityId": "1",
    "Temperature": 78   
}

Stream Analytics is reading the threshold values for each entity from a reference data blob which looks something like this.

 [{
     "SignalId": "1",
     "Entities": [{
         "Id": 1,
         "Threshold": 60
      }, {
          "Id": 2,
          "Threshold": 108
      }, {
          "Id": 3,
          "Threshold": 106
      }]
 }, {
     "SignalId": "2",
     "Entities": [{
         "Id": 1,
         "Threshold": 65
    }, {
        "Id": 2,
        "Threshold": 120
    }, {
        "Id": 3,
        "Threshold": 107
    }]
 }]

I've written a Stream Analytics Query to filter out event and insert an "Alarm" into an Azure SQL Database if temperature values are greater than their respective thresholds.

SELECT
    e.SignalId AS SignalId,
    e.EntityId AS EntityId,
    e.Temperature AS AttrValue,
    entities.ArrayValue.Threshold AS Threshold,
    SYSTEM.TIMESTAMP AS EventTimestamp
INTO
    output
FROM
    eventhub e
JOIN 
    referenceblob b 
ON 
    e.SignalId = b.SignalId
CROSS APPLY 
    GetElements(b.Entities) entities
WHERE
    e.EntityId = entities.ArrayValue.Id
AND
    e.Temperature > entities.ArrayValue.Threshold

What I need now is to put a Sliding Window (I assume) on this. I only want to create an "Alarm" if the temperature value for an entity crosses its respective threshold continuously for a period of 1 hour. The inserted row should contain data related to the last event in the window.

I'm new to Stream Analytics and T-SQL and, to be honest, didn't exactly understand how windowing functions work. Here are my questions...

1. If I get bad events (where temperature is greater than threshold) continuously for 30 minutes and then get a good event, is it possible to write a query such that a new window begins from the next bad event?

2. Is it possible to select data for the last event in the window? I tried using LAST() and LAG() but it gave me a compilation error saying "Cannot use GROUP BY without an Aggregate function". The problem is that I don't need an Aggregate function just the last event values.

I've been stuck on this problem for days and any help would be greatly appreciated.

Also, this is my first question so please forgive my noobishness

Answer 1

You can implement this with a sliding window. Sliding window produces output on every event and looks back for the duration provided. In your example, you want an output only when the reading was below threshold for 1 hour. A query like below should work.

SELECT
    Entity,
    max(Reading) MaxReading
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]
group by
    Entity,
    SlidingWindow(hour,1)
having
    MaxReading < 100