Azure Stream Analytics - Compare if current event temperature level is same with previous event temperature level

Question

I have sending device temperature and humidity value from downstream and having one ASA for handling value either under alert or normal status for sinking into Event Hub.

The device is sending Temperature and Humidity information that contains its Low Warning range, High Warning range, Low Critical range and High Critical range. The value is like this

The query is ASA is define to detect if current temperature is not "NORMAL" which is either "WARNING" or "CRITICAL", this event will be sink to Event Hub where another application will get this data for sending notification alert to person in charge. Query sample for warning level is:-

SELECT 
e.IoTHub.ConnectionDeviceId AS DeviceID,
e.Sensorname AS Sensorname,
e.SensorIPAddress AS SensorIPAddress,
CAST(e.Temperature AS FLOAT) AS Temperature,
DATEADD(HH,8,CAST(e.Timestamp AS DATETIME)) AS LocalTime,
CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) AS TempWarningLow,
CAST(e.TemperatureThreshold.Warning.High AS FLOAT) AS TempWarningHigh,
CAST(e.TemperatureThreshold.Critical.Low AS FLOAT) AS TempCriticalLow,
CAST(e.TemperatureThreshold.Critical.High AS FLOAT) AS TempCriticalHigh,
'WARNING' AS ALERT
INTO [TEMP-NOTIFICATION-WARNING] 
FROM [realcoming] AS e
WHERE (CAST(e.Temperature AS FLOAT) <= CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) AND CAST(e.Temperature AS FLOAT) > CAST(e.TemperatureThreshold.Critical.Low AS FLOAT)) OR (CAST(e.Temperature AS FLOAT) >= CAST(e.TemperatureThreshold.Warning.High AS FLOAT) AND Sensorname IS NOT NULL AND 
SensorIPAddress IS NOT NULL AND 
Temperature IS NOT NULL

I have test with sample json file in ASA sample testing and it show correctly which data is warning, which data is critical, which data is normal:-

{"Sensorname":"Ultrasonic Surface","SensorIPAddress":"10.12.115.18","Temperature":33.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:25.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Infra Red 1A","SensorIPAddress":"10.12.115.35","Temperature":46.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:27.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Infra Red 1A","SensorIPAddress":"10.12.115.35","Temperature":42.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:29.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":23.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:31.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":63.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:33.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":32.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:35.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":23.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:37.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Room 2A","SensorIPAddress":"10.12.115.11","Temperature":59.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:39.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":40.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:41.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Infra Red 1A","SensorIPAddress":"10.12.115.35","Temperature":12.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:43.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Infra Red 1A","SensorIPAddress":"10.12.115.35","Temperature":54.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:45.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Room 2A","SensorIPAddress":"10.12.115.11","Temperature":64.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:47.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Ultrasonic Surface","SensorIPAddress":"10.12.115.18","Temperature":78.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:49.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":64.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:51.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Infra Red 1A","SensorIPAddress":"10.12.115.35","Temperature":64.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:53.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}
{"Sensorname":"Laser Room 2B","SensorIPAddress":"10.12.115.20","Temperature":35.0,"TemperatureThreshold":{"Warning":{"Low":"20","High":"80"},"Critical":{"Low":"10","High":"90"}},"Timestamp":"2016-12-20T13:15:55.0000000Z","IoTHub":{"ConnectionDeviceId":"TEMP001"}}

The interval between each event is 2 minutes. So, if first event is "WARNING" it will sink to Event Hub, and if the next event is still "WARNING" the event will be skip as user need one time notification if reach first "WARNING" or "CRITICAL".

I have search and found to compare with previous event, I need to use "LAG" in my query.

LAG(CAST(e.Temperature AS FLOAT) <= CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) AND CAST(e.Temperature AS FLOAT) > CAST(e.TemperatureThreshold.Critical.Low AS FLOAT)) OR (CAST(e.Temperature AS FLOAT) >= CAST(e.TemperatureThreshold.Warning.High AS FLOAT)) OVER (PARTITION BY Temperature LIMIT DURATION(minute, 2))

But this query failed in format. If only I know how if the "WARNING" that I static set will be available in previous events so I only can compare "ALERT" variable which can shorter the query.

The "ALERT" variable is set as static. Anyone know how to compare?

One quick question:- Is anything that we define in the query will be pass to previous event? Or does the previous event only having information that coming from IoT Hub? In my case, if the temperature is under "WARNING" state, does in previous event, it will have "ALERT" with "WARNING" value?

My reference article:

https://msdn.microsoft.com/en-us/library/azure/dn966240.aspx

https://stackoverflow.com/questions/36525338/azure-stream-analytics-with-lag-statement-generates-two-output-events-for-every

https://talkingaboutdata.wordpress.com/2015/08/13/azure-streaming-analytics-lag-function-with-example/

Answer 1

You LAG method having syntax issue, Check LAG parameters.

I just modified your query in ASA query window and fixed syntax error.

SELECT 
e.IoTHub.ConnectionDeviceId AS DeviceID,
e.Sensorname AS Sensorname,
e.SensorIPAddress AS SensorIPAddress,
CAST(e.Temperature AS FLOAT) AS Temperature,
DATEADD(HH,8,CAST(e.Timestamp AS DATETIME)) AS LocalTime,
CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) AS TempWarningLow,
CAST(e.TemperatureThreshold.Warning.High AS FLOAT) AS TempWarningHigh,
CAST(e.TemperatureThreshold.Critical.Low AS FLOAT) AS TempCriticalLow,
CAST(e.TemperatureThreshold.Critical.High AS FLOAT) AS TempCriticalHigh,
'WARNING' AS ALERT
INTO
[TEMP-NOTIFICATION-WARNING]
FROM
[realcoming] AS e

WHERE 
(
CAST(e.Temperature AS FLOAT) <= CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) 
AND CAST(e.Temperature AS FLOAT) > CAST(e.TemperatureThreshold.Critical.Low AS FLOAT)
)
OR 
(CAST(e.Temperature AS FLOAT) >= CAST(e.TemperatureThreshold.Warning.High AS FLOAT) 
AND Sensorname IS NOT NULL AND SensorIPAddress IS NOT NULL AND 
Temperature IS NOT NULL
)
AND LAG(CAST(e.Temperature AS FLOAT)) OVER (PARTITION BY e.IoTHub.ConnectionDeviceId 
LIMIT DURATION(minute, 2)) >= 
CAST(e.TemperatureThreshold.Warning.Low AS FLOAT) 

If you want to add multiple conditions with LAG method, I will recommend write your query using WITH then use further conditions, it will make your query more readable and maintainable.