Get the period of events that are received within x time from each other

Question

Given the following input:

[
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:00:00", "Event" : 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:01:00", "Event" : 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:05:00", "Event" : 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:10:00", "Event" : 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:15:00", "Event" : 1}
]

I want to create the output:

[
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:00:00", "Event" : 1, "Value": 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:05:00", "Event" : 1, "Value": 0}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:10:00", "Event" : 1, "Value": 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:11:00", "Event" : 1, "Value": 0}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:15:00", "Event" : 1, "Value": 1}
    { "DeviceId" : "AA" : "Date": "2022-04-25 00:16:00", "Event" : 1, "Value": 0}
]

When event 1 is received for the first time a period needs to start (Value = 1 in the output events). When the next event is received within 5 seconds the period is extended until no event is received for 5 seconds then the period needs to stop (Value = 0 in the output events). The period cannot be smaller then 1 second.

How can I achieve this behaviour? I was thinking about using a HoppingWindow with a Lag over the result but couldn't get this working. Do you have any clues?

I also want to make the rule "next event withing 5 seconds" flexible per device. Not sure if this is even possible since the Window functions have a compile time duration.

Thanks for your help!

Answer 1

You can try with LAG analytic operator or LAST analytic operator.

LAG: It allows to look up previous event in an event stream within certain constraints.

LAST: It allows to look up the most recent event in an event stream within defined constraints.

Thanks to @Sivamuthu Kumar for his blog on Azure Stream Analytics - Alerts.

Like in the above said, with the help of WITH clause (CTE) partition by the deviceId and the time interval as per the scenario.