Btukfyl

I do not see a way to analyse a stream for the absence of a specific event with azure stream analytics query language.
The stream may contain DeviceAlive and BeaconDetected events containing a DeviceId and in case of BeaconDetected also a BeaconId. Now I want to generate an error event if the DeviceAlive event is missing.

How can I achieve this?
I tried to use reference data with all valid deviceIds.
But I am not allowed to do a linq-wise "contains" query like this

SELECT * FROM

     inputStream

WHERE DeviceId IN (SELECT Id FROM DeviceReferenceData)

How can I do such a query. Is this possible by joining the inputStream with the DeviceReferenceData table?
I guess I just do not see the obvious.

The events e.g. are looking like this:

 {

    "EventType": "DeviceAlive",

    "DeviceId": "winiot-pi",

    "EventEnqueuedUtcTime": "2018-11-19T11:00:20.5594584+01:00"

 },

 {

    "EventType": "BeaconDetected",

    "BeaconId": "2",

    "DeviceId": "winiot-pi",

    "EventEnqueuedUtcTime": "2018-11-19T11:00:20.5594584+01:00"

 }

Doing a query like this, does not produce the expected result:

SELECT

    iothub.*,r.id as rerId

into output

FROM

    iothub TIMESTAMP BY EventEnqueuedUtcTime

Left OUTER Join devicesReference r on iothub.DeviceId = r.Id

This only returns a NULL referenceId for every DeviceAlive event. The output in csv:

eventenqueuedutctime;EventType;BeaconId;DeviceId;SignalStrength;rerid

19.11.2018 10:00:20;BeaconDetected;1;winiot-pi;-40;winiot-pi

19.11.2018 10:00:20;BeaconDetected;1;winiot-pi2;-80;winiot-pi2

19.11.2018 10:00:20;ReceiverDeviceAlive;winiot-pi;winiot-pi;;

19.11.2018 10:00:21;ReceiverDeviceAlive;winiot-pi2;winiot-pi2;;

19.11.2018 10:00:21;BeaconDetected;2;winiot-pi;-80;winiot-pi

19.11.2018 10:00:21;BeaconDetected;2;winiot-pi2;-40;winiot-pi2

19.11.2018 10:00:25;ReceiverDeviceAlive;winiot-pi;winiot-pi;;

19.11.2018 10:00:25;ReceiverDeviceAlive;winiot-pi2;winiot-pi2;;

19.11.2018 10:00:31;BeaconDetected;1;winiot-pi2;-40;winiot-pi2

19.11.2018 10:00:31;BeaconDetected;1;winiot-pi;-80;winiot-pi

19.11.2018 10:00:32;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:00:32;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:00:33;BeaconDetected;2;winiot-pi2;-80;winiot-pi2

19.11.2018 10:00:36;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:00:46;BeaconDetected;2;winiot-pi2;-80;winiot-pi2

19.11.2018 10:00:46;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:00:57;BeaconDetected;2;winiot-pi2;-80;winiot-pi2

19.11.2018 10:00:57;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:01:07;BeaconDetected;2;winiot-pi2;-80;winiot-pi2

19.11.2018 10:01:07;BeaconDetected;2;winiot-pi;-40;winiot-pi

19.11.2018 10:01:20;ReceiverDeviceAlive;winiot-pi2;winiot-pi2;;

19.11.2018 10:01:30;ReceiverDeviceAlive;winiot-pi2;winiot-pi2;;

But what I need is the information on every time window also if the window does not contain any events. We can break it down to that question I guess:
How to query and see also time windows that have none of the desired events.
Is that altogether possible?

Thank you for your help.

Based on your requirements, maybe you could use JOIN reference Data to find the deviceId which is missing in the reference data collection. Please try below sql:

SELECT

    jsoninput.*,r.id as rerId

into output

FROM

    jsoninput

Left OUTER Join jsonreference r on jsoninput.id = r.id

where r.id is null

It is a good advice to talk to a colleague, even if he does not fully understand what you are talking about. ;-)
Here is the solution for generating an error event by detecting the absence of alive events in a 30 second window for a dedicated device with the help of a reference device table.
Those links helped me a lot understanding more of it:

azure stream analytics query to detect missing alive event for a specific device

how to find absence of signal in a stream analytics job

WITH OneEvent AS /* generate one event per period, any event */

(

            SELECT 

                COUNT(*) As eventCount,

                System.Timestamp as EndOfWindow

            FROM iothub TIMESTAMP BY EventEnqueuedUtcTime

            GROUP BY TumblingWindow(s, 30)

),

AllReferenceDevices AS /* generate one event per deviceId per period */

(

            SELECT devicesReference.Id, OneEvent.EndOfWindow

            FROM OneEvent JOIN devicesReference

            ON OneEvent.EndOfWindow = OneEvent.EndOfWindow

),

/* Select only the devices where we cannot find an event for */

DeviceConnectivityErrorDetection AS

(

    SELECT 

        'DeviceConnectivityErrorDetected' AS EventType,

        AllReferenceDevices.Id as FromDeviceId,

        AllReferenceDevices.Id as ToDeviceId,

        AllReferenceDevices.EndOfWindow as EventEnqueuedUtcTime

    FROM 

        AllReferenceDevices 

      LEFT join  iothub  TIMESTAMP BY EventEnqueuedUtcTime

   ON DATEDIFF(s, iothub, AllReferenceDevices ) BETWEEN 0 and 30 

            AND iothub.DeviceId = AllReferenceDevices.Id

    WHERE iothub IS NULL

 ) 





SELECT  *

INTO ReceiverDeviceConnectivityErrorDetectedOutput

FROM DeviceConnectivityErrorDetection