Search code examples
azureazure-eventhubazure-stream-analytics

Asset Tracking with Azure Stream Analytics


I have an event Hub and a stream analytics job sending data to Power BI. I was wondering what would be the best way to configure the event hub / for asset tracking?

e.g I have multiple clients sending to the event hub -> stream analytic job and I want to be able to determine if a client with a particular ID goes offline?

Cheers!


Solution

  • If the list of IDs is relatively static, then you can use reference data join to output all IDs that are missing in each time window.

    If you want to infer the IDs from the stream itself, and want to detect when an ID that was active in previous window is not active in current window, you can use stream join. Here is an example

    with MissingAssets as
    (
    select
        PreviousWindowSignal.signalTime,
        PreviousWindowSignal.AssetId
    from
        AssetSignalStream PreviousWindowSignal Timestamp by signalTime
    left outer join
        AssetSignalStream CurrentWindowSignal Timestamp by signalTime
     on
        PreviousWindowSignal.AssetId = CurrentWindowSignal.AssetId
        and datediff(second,PreviousWindowSignal,CurrentWindowSignal) between 1 and 300
    where
        CurrentWindowSignal.AssetId is null
     )
    
     select
        AssetId,
        max(signalTime) MostRecentSignalInWindow 
    
     from 
        MissingAssets 
      group by
        AssetId,
        TumblingWindow(ss,300)