LAST_TIME_SERIES

The last value per time interval. The size of the time intervals is configurable & dynamic; the time interval used will change depending on how many points you wish to hold per aggregation key. ‌You can change the time interval and/or the number of points per interval to suit your graph.

Syntax

LAST_TIME_SERIES([MAX POINTS, INTERVALS, ]TIME, VALUE)

Arguments

MAX POINTS: The maximum amount of points to hold before reducing the resolution to the next interval size. Default Value: 300 INTERVALS: An array of integers representing the time interval buckets to return data for. If the aggregation has more than MAX POINTS values in the given window it will use the next interval from this array to reduce the amount of points to comply with. Default: [60000, 300000, 600000, 1800000, 3600000, 10800000, 21600000, 43200000, 86400000] TIME: An expression returning the time value that the VALUE is to be associated with. VALUE: An expression returning the value to aggregate in the provided TIME bucket.

Returns

An array of key value pairs, the key is the time (in epoch ms) and the value is of type VALUE and contains the average of the values within that time frame.

Notes

This function is non-deterministic. Time intervals are calculated from the start of epoch time ( January 1, 1970 12:00:00 AM).

‌Example

Data

[
   {
      "serverIp":"10.0.0.1",
      "time":1628894700000,
      "cpuUsage":52.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894760000,
      "cpuUsage":2.4
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894820000,
      "cpuUsage":99.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894880000,
      "cpuUsage":99.6
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628894940000,
      "cpuUsage":12.3
   },
   {
      "serverIp":"10.0.0.1",
      "time":1628895000000,
      "cpuUsage":55
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894700000,
      "cpuUsage":2.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894760000
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894820000,
      "cpuUsage":9.3
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894880000,
      "cpuUsage":9.6
   },
   {
      "serverIp":"10.0.0.2",
      "time":1628894940000,
      "cpuUsage":2.3
   }
]

Query

Find the last VALUE entry in the desired time interval:

SET
   DATE_TIME = UNIX_EPOCH_TO_DATE(data.time);
SELECT
   data.serverIp AS serverip:STRING,
   LAST_TIME_SERIES(2, [60000, 300000], data.time, data.cpuUsage) AS last_time_series_data_time__data_cpuusage 
FROM
   "TIME_SERIES_DATA_w_NULL" 
GROUP BY
   data.serverIp

Results

{
   "serverip":"10.0.0.2",
   "last_time_series_data_time__data_cpuusage":[
      {
         "value":2.3,
         "key":1628894700000
      }
   ]
}{
   "serverip":"10.0.0.1",
   "last_time_series_data_time__data_cpuusage":[
      {
         "value":12.3,
         "key":1628894700000
      },
      {
         "value":55,
         "key":1628895000000
      }
   ]
}

Related Functions

LAST MAX_TIME_SERIES MIN_TIME_SERIES

Dialog