Get Last Value of a Time Series with Azure TimeSeries Insights

Question

How can i query the last (most recent) event along with it's timestamp within a time series?

The approach described here does not work for me as i can not guarantee that the most recent event is within a fixed time window. The event might have been received hours or days ago in my case.

The LAST() function return the last events and the Get Series API should preserve the actual event time stamps according to the documentation but i am a bit confused about the results i am getting back from this API. I get multiple results (sometimes not even sorted by timestamp) and have to find out the latest value on my own. Also i noticed that the query result does not actually reflect the latest ingested value. The latest ingested value is only contained in the result set if i ingest this value multiple times.

It there any more straight-forward or reliable way to get the last value of a time series with Azure Time Series Insights?

score 2 · Accepted Answer · edited Mar 24 '21 at 19:21

The most reliable way to get the last known value, at the moment, is to use the AggregateSeries API.

You can use the last() aggregation in a variable calculating the last event property and the last timestamp property. You must provide a search span in the query, so you will still have to "guess" when the latest value could have occurred.

Some options are to always have a larger search span than what you may need (e.g. if a sensor sends data every day, you may input a search span of a week to be safe) or use the Availability API to get the time range and distribution of the entire data set across all TSIDs and use that as the search span. Keep in mind that having large search spans will affect performance of the query.

Here's an example of a LKV query:

"aggregateSeries": {

    "searchSpan": {

        "from": "2020-02-01T00:00:00.000Z",

        "to": "2020-02-07T00:00:00.000Z"

    },

    "timeSeriesId": [

        "motionsensor"

    ],

    "interval": "P30D",

    "inlineVariables": {

        "LastValue": {

            "kind": "aggregate",

            "aggregation": {

                "tsx": "last($event['motion_detected'].Bool)"

            }

        },

        "LastTimestamp": {

            "kind": "aggregate",

            "aggregation": {

                "tsx": "last($event.$ts)"

            }

        }

    },

    "projectedVariables": [

        "LastValue", 

        "LastTimestamp"

    ]

}

Get Last Value of a Time Series with Azure TimeSeries Insights

1 Answers1