locked
How to detect absence of events RRS feed

  • Question

  • Hi,

    I would like to create a query to detect the absence of events. I have a number of devices sending periodic events to the Event Hub. Normally this happens every n seconds. I would like a query that detects that for (p * n) seconds no events for a specific device came, and generate an output.

    I came up with a query like this:

    SELECT     
      Hostname,    
      MAX(Timestamp) AS Last,
      System.Timestamp AS CurrentTime,
      DATEDIFF(second, MAX(Timestamp), System.Timestamp) AS Count
    INTO
      [output]
    FROM
      [input] TIMESTAMP BY Timestamp
    GROUP BY
      Hostname,
      TumblingWindow(day, 1)    


    However, I do not succeed in selecting only those entries were Count is greater than (p * n). For instance when I add something like

    SELECT
      Count > 60

    No results are returned anymore.

    What can I do for this scenario?

    Thursday, March 2, 2017 10:19 AM

Answers

  • Actually, I found a different forum post about my problem that worked for me:

    https://social.msdn.microsoft.com/Forums/en-US/77469ff0-3acb-4717-a69b-7d3d1c49949a/alert-when-no-input-is-received-for-x-minutes?forum=AzureStreamAnalytics

    Monday, March 6, 2017 10:55 AM

All replies

  • Think you can do it using the lag function

     WITH SelectPreviousEvent AS

        (
        SELECT  
         Hostname, Timestamp,  LAG(Timestamp) OVER (PARTITION BY Hostname LIMIT DURATION(day, 1) WHEN Timestamp IS NOT NULL) as lasttime  
         FROM input TIMESTAMP BY [Timestamp]
    )

    select Hostname, max( datediff(s, lasttime, Timestamp)) from selectpreviousevent
    group by Hostname, tumblingwindow(day,1)

    having datediff(s, lasttime, Timestamp)>60

    But it'll only return a "window" that has already happend... don't know if it possible to detect the current "window" since the select will not run without events

    Thursday, March 2, 2017 8:56 PM
  • Unfortunately, it refuses the 'lasttime' on the final line in the 'having' clause.  it complains about

    "Column 'lasttime' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

    Friday, March 3, 2017 1:44 PM
  • Actually, I found a different forum post about my problem that worked for me:

    https://social.msdn.microsoft.com/Forums/en-US/77469ff0-3acb-4717-a69b-7d3d1c49949a/alert-when-no-input-is-received-for-x-minutes?forum=AzureStreamAnalytics

    Monday, March 6, 2017 10:55 AM