Azure Stream Analytics查询以检测特定deviceId缺少的活动事件 [英] Azure Stream Analytics query to detect missing alive event for a specific deviceId
问题描述
我没有看到一种使用天蓝色流分析查询语言来分析缺少特定事件的流的方法.该流可能包含DeviceAlive和BeaconDetected事件，这些事件包含DeviceId，在BeaconDetected的情况下，还包含BeaconId.现在，如果缺少DeviceAlive事件，我想生成一个错误事件.
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.
我该如何实现?我尝试将参考数据与所有有效的deviceId一起使用.但是我不允许这样进行linq明智的包含"查询
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)
如何进行此类查询.通过将inputStream与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
这仅为每个DeviceAlive事件返回一个NULL referenceId.csv中的输出:
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?
谢谢您的帮助.
推荐答案
与同事交谈是一个很好的建议，即使他不完全了解您在说什么.;-)这是用于通过参考设备表在专用设备的30秒窗口中检测到未激活事件来生成错误事件的解决方案.这些链接使我对它有了更多的了解:
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:
