Axibase Time Series Database

Series: query

Description

Retrieves time series objects for the specified metric, entity, tags, and date range. Applies common time series transformations including aggregation, interpolation, downsampling etc.

Quick Start

Basic query:

[{
  "metric":    "mpstat.cpu_busy",
  "entity":    "nurswgvml007",
  "startDate": "2018-05-20T00:00:00Z",
  "endDate":   "2018-05-20T01:00:00Z"
}]

Query response:

[{
  "metric":    "mpstat.cpu_busy",
  "entity":    "nurswgvml007",
  "data": [
    {"d":"2018-05-20T00:01:30Z", "v":24.2},
    {"d":"2018-05-20T00:03:15Z", "v":39.8},
    {"d":"2018-05-20T00:05:00Z", "v":39.1}
  ]
}]

Request

Method Path Content-Type Header
POST /api/v1/series/query application/json

The request payload is a JSON array containing query objects.

[{
  /* query 1: filter, transform, control */
},{
  /* query 2: filter, transform, control */
}]

The query contains filter fields to find time series in the database, transform fields to modify the matched series, and control fields to order and format the results.

Filters

Base Filter

Field Type Description
metric string [Required] Metric name.
type string Data type: HISTORY, FORECAST, FORECAST_DEVIATION.
Default: HISTORY

Entity Filter

"entity": "nurswgvml007"

"entityGroup": "nur-prod-servers"

"entityExpression": "tags.location LIKE 'SVL*'"

Queries of FORECAST and FORECAST_DEVIATION type do not support wildcards in the entity name and tag values. Tag value * matches all tags.

Tag Filter

Field Type Description
tags object Object with name:value fields.
Matches series that contain the specified series tags.
Tag values support ? and * wildcards.
exactMatch boolean tags match operator. Exact match if true, partial match if false.
Default: false (partial match).
Exact match selects series with exactly the same tags as requested.
Partial match selects series with tags that contain requested tags but can also include additional tags.
tagExpression string An expression to include series with tags that satisfy the specified condition.
"tags": { "mount_point": "/", "file_system": "/dev/sda1" }

Tag Expression

  • The tagExpression can refer to series tags by name using tags.{name} syntax.
  • The series record must satisfy both the tags object and the tagExpression condition to be included in the results.
  • Supported operators: LIKE, NOT LIKE, IN, NOT IN, =, !=, >=, >, <=, <.
  • Supported functions: LOWER.
  • Wildcards ? and * are supported by LIKE and NOT LIKE operators. Symbols ? and * are treated as regular characters when used with comparison operators =, !=, >=, >, <=, <.
"tagExpression": "tags.file_system LIKE '/dev/sda*'"

"tagExpression": "tags.file_system NOT IN ('/dev/sda1', '/dev/sda2')"

Date Filter

"startDate": "2018-05-30T14:00:00Z",
"endDate":   "2018-05-30T15:00:00Z"

Last Insert Filter

The filter selects entire series based on the timestamp of the most recent sample.

Field Type Description
minInsertDate string Include series if the timestamp of the latest sample is equal or greater than minInsertDate.
ISO format date or a calendar keyword.
Examples: 2017-10-01T00:00:00Z, current_day.
maxInsertDate string Include series if the timestamp of the latest sample is less than maxInsertDate.
ISO format date or a calendar expression.
Examples: 2017-10-01T00:00:00Z, now - 1*DAY.

If both fields are specified, the series is included if it satisfies both conditions.

"minInsertDate": "2019-01-01T00:00:00Z",
"maxInsertDate": "current_day"

Forecast Filter

Name Type Description
forecastName string Unique forecast name. Identifies a custom forecast by name. If forecastName is not set, then the default forecast computed by the database is returned. forecastName is applicable only when type is set to FORECAST or FORECAST_DEVIATION.

Versioning Filter

Name Type Description
versioned boolean Returns version status, source, and change date if the metric is versioned.
Default: false.
versionFilter string Expression to filter value history (versions) by version status, source or time, for example: version_status = 'Deleted' or version_source LIKE '*user*'. To filter by version time, use date() function, for example, version_time > date('2018-08-11T16:00:00Z') or version_time > date('current_day'). The date() function accepts calendar keywords.

Sample Filter

Name Type Description
sampleFilter string Boolean expression applied to each time:value sample. Samples that satisfy the condition are included in the result.
Available fields: value and date.
The value field in the expression refers to the numeric value of the sample.
The date field represents sample timestamp as an instance of the DateTime object.

Sample Filter Processing Rules:

  • The filter is applied before series transformations (interpolation, aggregation, etc).
  • In case of a versioned metric in versioned=true mode, the filter checks only the last sample recorded for the given time. If the last sample satisfies the filter, all versions for that time are included.
  • The date field is an instance of the DateTime object and can be tested with various date functions.

Examples:

  • value > 0: Retrieves samples which are positive numbers.
  • value > 2 && value <= 3: Retrieves samples within the specified range.
  • Math.sin(value) < 0.5: Math functions are supported.
  • Double.isNaN(value): Only NaN values and deleted values pass this check.
  • date.is_weekday(): Retrieves samples recorded on week days within the selection interval.
  • !date.is_exceptionday(): Retrieves samples recorded on regular calendar days.
  • date.hourOfDay >= 14: Retrieves samples recorded at 14:00 or later each day.
  • date.timeOfDay BETWEEN '14:00' AND '15:00': Retrieves samples recorded between 14:00 and 15:00 (inclusive) each day.
  • filter_by_time_of_day('14:00', '15:00'): Retrieves samples recorded between 14:00 and 15:00 (inclusive) each day using a filter function.
  • date.to_timezone('US/Eastern') BETWEEN '09:30' AND '16:00' && date.to_timezone('US/Eastern').is_weekday('nyse')

Transformations

Name Description
interpolate Fill missing values in the detailed data using a linear or step-like interpolation functions.
group Merge multiple series into one series.
rate Compute difference between consecutive samples per unit of time (rate period).
aggregate Group detailed values into periods and calculate statistics for each period.
smooth Smooth time series.
downsample Reduce time series cardinality by filtering out some samples.
forecast Predict future values based on trends and recurring patterns.

The default transformation sequence is as follows:

  1. interpolate
  2. group
  3. rate
  4. aggregate
  5. smooth
  6. downsample
  7. forecast

The interpolate transformation, if requested, is applied to detailed data before the values are passed to subsequent stages.

The default sequence can be modified by adding an transformationOrder field.

"transformationOrder": ["group", "forecast"]

Control Fields

Name Type Description
limit integer Maximum number of time:value samples returned for each series. Default: 0 (no limit).
The limit is applied from the beginning of the selection interval if the direction is ASC and from the end if the direction is DESC.
For example, limit=1 with direction=DESC returns the most recent last value.
Limit is not applied if the parameter value <= 0.
direction string Order for applying the limit parameter: ASC: ascending, DESC: descending. Default: DESC.
The returned data values are sorted in ascending order regardless of direction.
limit=10 means the most recent 10 values.
seriesLimit integer Maximum number of series returned. Default: 0 (no limit).
The database raises a processing error if series count exceeds 10000 for queries that fetch data for an non-versioned metric without limit.
cache boolean If true, execute the query against the Last Insert table, which is the fastest way to retrieve the last value for a query. Default: false.
Values in the Last Insert table can be delayed up to 15 seconds , controlled with last.insert.write.period.seconds setting. Only 1 value is returned for each series.
requestId string Optional identifier used to associate query object in request with one or multiple series objects in response.
timeFormat string Time format for a data array. iso or milliseconds. Default: iso.
addMeta boolean Include metric and entity metadata (fields and tags) under the meta object in the response. Default: false.
transformationOrder array List of transformation names such as interpolate, aggregate to control the order in which the transformations are applied to data.
Example: "transformationOrder": ["group", "forecast"]
timezone string Time zone ID applied in transformations for calendar alignment and keyword evaluation.

Response

The response contains an array of series objects, each containing series identifiers and request fields and an array of timestamped value objects.

Name Type Description
entity string Entity name
metric string Metric name
tags object Object containing series tags.
type string Type of inserted data: HISTORY, FORECAST.
aggregate string Type of statistical aggregation: DETAIL, AVG, MAX, etc.
data array Array of Value objects.
meta object Metric and entity metadata fields, if requested with the addMeta parameter.

Value Object

  • The value object contains a sample time and a numeric (v field) or text (x field) value.
  • Specify sample time in Unix time with millisecond precision (t field) or ISO format (d field).
Name Type Description
t integer Sample Unix time with millisecond precision.
d string Sample time in ISO format.
v number Numeric sample value at time t/d.
The field is set to null if the value is Not a Number: {"d":"2017-09-14T17:00:03.000Z","v":null}
x string Text sample value at time t/d.
version object Object containing version source and status fields for versioned metrics.

Example

Request

URI

POST /api/v1/series/query

Payload

[{
  "startDate": "2017-09-14T17:00:00Z",
  "endDate":   "2017-09-14T18:00:00Z",
  "entity":    "nurswgvml007",
  "metric":    "mpstat.cpu_busy"
}]

Response

[{
    "entity": "nurswgvml007",
    "metric": "mpstat.cpu_busy",
    "tags": {},
    "type": "HISTORY",
    "aggregate": {
      "type": "DETAIL"
    },
    "data": [
        {"d":"2017-09-14T17:00:03.000Z","v":24.24},
        {"d":"2017-09-14T17:00:19.000Z","v":39.8},
        {"d":"2017-09-14T17:00:35.000Z","v":39.18}
    ]
}]

curl Example

curl https://atsd_hostname:8443/api/v1/series/query \
  -k --user {username}:{password} \
  --header "Content-Type: application/json" \
  -d '[{"metric":"mpstat.cpu_busy", "entity":"nurswgvml007", "startDate":"previous_day", "endDate": "now"}]' > response.json

Java Example

Python Example

Additional Examples

Time Range

Series Tags

Special Values

Entity Filter

Multiple Queries

Control Fields

Regularize / Downsample

Aggregation

Period

Group

Rate

Forecast

Versioning

← insert →