anomalydetection.asaql

WITH AggregationStep AS 
(
     SELECT
           System.Timestamp as tumblingWindowEnd,
           AVG(temperature) as avgTemperature
     FROM TemperatureSensor
     GROUP BY TumblingWindow(second, 5)
),

FillInMissingValuesStep AS
(
      SELECT
            TopOne() OVER (ORDER BY tumblingWindowEnd DESC) AS lastEvent
     FROM AggregationStep
     GROUP BY HOPPINGWINDOW(second, 300, 5)
),

AnomalyDetectionStep AS
(
      SELECT
            lastEvent.tumblingWindowEnd as anomalyTime,
            system.timestamp as anomalyDetectedTime,
            lastEvent.avgTemperature as avgTemperature,
            ANOMALYDETECTION(lastEvent.avgTemperature) OVER (LIMIT DURATION(minute, 10)) as scores
      FROM FillInMissingValuesStep
),

OutputSet AS
(
    SELECT
      anomalyTime,
      anomalyDetectedTime,
      avgTemperature,
      CAST(GetRecordPropertyValue(scores, 'BiLevelChangeScore') as float) as [Bi Level Change],
      CAST(GetRecordPropertyValue(scores, 'SlowPosTrendScore') as float) as [Slow Postive Trend],
      CAST(GetRecordPropertyValue(scores, 'SlowNegTrendScore') as float) as [Slow Negative Trend]
    FROM AnomalyDetectionStep
)

SELECT
      anomalyTime,
      anomalyDetectedTime,
      avgTemperature,
      [Bi Level Change],
      [Slow Postive Trend],
      [Slow Negative Trend],
      CASE          
        WHEN [Bi Level Change]>3.25 AND [Bi Level Change]> [Slow Postive Trend] AND [Bi Level Change]> [Slow Negative Trend]
            THEN 'Bi Level Change'
        WHEN [Slow Postive Trend]>3.25 AND [Bi Level Change]< [Slow Postive Trend] AND [Slow Postive Trend]> [Slow Negative Trend]
            THEN 'Slow Postive Trend'
        WHEN [Slow Postive Trend]>3.25 AND [Bi Level Change]< [Slow Negative Trend] AND [Slow Postive Trend]< [Slow Negative Trend]
            THEN 'Slow Negative Trend'
        ELSE ''
       END AS anomalyType
INTO TelemetryAlert
FROM OutputSet