How to detect if no data ingested in any IoTHub Device using Stream analytics?

Question

Basically I have multiple devices in IoTHub with 30 partition. Now, I want to detect if there is no data being ingested to any devices after 10 mins using Stream Analytics. Once detected, I want to select what device is it and send that information to Azure function for Alert.

The query is a bit tricky given that I'm new with stream analytics. Here is what I came up so far but it seems the out is not what I expected.

SELECT 
  t1.[IoTHub].[ConnectionDeviceId] as DeviceId
INTO 
  [NoDataAlertFunctionOutput]
FROM 
  [iot-hub-data-flow] t1 TIMESTAMP BY Timestamp
LEFT OUTER JOIN [iot-hub-data-flow] t2 TIMESTAMP BY Timestamp
  ON
  t1.[IoTHub].[ConnectionDeviceId]=t2.[IoTHub].[ConnectionDeviceId]
AND
  DATEDIFF(minute,t1,t2) BETWEEN 1 and 10
  WHERE t2.[IoTHub].[ConnectionDeviceId] IS NULL

I will greatly appreciate any suggestion or comment.

Here are the references I trying to follow:

• Stream Analytics Common Query
• Process real-time IoT Data

On the other hand, is there a built-in functionality in IoTHub to detect if no data ingested at certain time period?

Answer 1

From the ASA perspective, I would try this pattern instead. I'm not 100% sure that it checks all your requirements, but I think it's an interesting direction to explore.

• Using a hopping window, every minute we scan the last 20
• First we go find the last event in that window
• Then we check if it's been more than 10 minutes if it was sent

WITH CTE AS (
    SELECT
        System.Timestamp() AS Window_end,
        DATEADD(minute,-10,System.Timestamp()) AS Window_10,
        TopOne() OVER (ORDER BY Timestamp DESC) AS Last_event
    FROM
        [iot-hub-data-flow] t TIMESTAMP BY Timestamp
    GROUP BY
        HOPPINGWINDOW(minute, 20, 1)
        -- Every 1 minute, check the last 20 minutes 
)
SELECT
    Last_event.DevideId
FROM CTE
WHERE Last_event.Timestamp < Window_10

Note that after 20 minutes, the alert stop being emitted.