Database Series Functions

Overview

These functions retrieve series records from the database at any stage of the rule evaluation process.

The db_last and db_statistic functions retrieve the last stored value or calculate statistics from other stored values. The queried series can be different from the series in the current window.

Related functions:

value

`db_last`

db_last(string metric [, string entity [, string tags | map tags]]) number

Retrieves the most recent (last) value stored in the database for the specified series. Returns Double.NaN if no matching series is found.

The metric argument specifies the name of the metric for which to retrieve the value. If no other arguments are specified, the data is loaded for same entity and tags as defined in the current window.

value > 60 && db_last('temperature') < 30

As an alternative, if the specified metric is received in the same series command, use the value() function. The value() function returns metric values set in the command without querying the database.

To load data for an entity, other than the entity in the current window, specify entity name as a literal string or a field in the second argument.

Example:

value > 60 && db_last('temperature', 'sensor-01') < 30

value > 60 && db_last('temperature', tags.target) < 30

To retrieve data for different series tags, specify them in the third argument:

'tag1=value1' to select series with tag1 equal to value1.
'tag1=*' to select series with tag1 containing any value.
Empty string '' to select series without any series tags.
String containing one or multiple name=value pairs separated by comma: 'tag1=value1,tag2=value2'.
Key-value map: ['tag1':'value1','tag2':'value2'].
The tags field representing the grouping tags of the current window.

Example:

value > 60 && db_last('temperature', 'sensor-01', 'stage=heating,unit=c') < 30

value > 60 && db_last('temperature', 'sensor-01', ['stage' : 'heating', 'unit': 'c']) < 30

`db_statistics`

db_statistics(string interval, [ string metric, [string entity, [string tags | map tags]]]) number

Returns an object with all statistics that can be retrieved with the db_statistic function. The statistics can be accessed by name as the object fields.

avg() > 60 && db_statistics('3 hour', 'temperature').avg > 30

The query must match only one series.

The object can be initialized as a local variable and re-used in the expression.

// stats = db_statistics('3 hour', 'temperature')
avg() > 60 && stats.avg > 30 && stats.max < 90

The object contains the following fields:

- min                // Minimum value
- max                // Maximum value
- avg                // Average value
- count              // Sample count
- sum                // Sum of values
- median             // Median value (50% percentile)
- stdDev             // Standard deviation
- first              // First sample value (ordered by time)
- last               // Last sample value  (ordered by time)
- delta              // Difference between the first and last values
- counter            // Sum of positive differences between consecutive sample values
- wavg               // Count-weighted average (most recent samples weigh more)
- wtavg              // Time-weighted average  (most recent samples weigh more)
- slope              // Linear regression slope
- intercept          // Linear regression intercept
- percentile99_9     // 99.9% percentile
- percentile99_5     // 99.5% percentile
- percentile99       // 99.0% percentile
- percentile95       // 95.0% percentile
- percentile90       // 90.0% percentile
- percentile75       // 75.0% percentile
- percentile50       // 50.0% percentile (same as median)
- percentile25       // 25.0% percentile
- percentile10       // 10.0% percentile
- percentile5        //  5.0% percentile
- percentile1        //  1.0% percentile
- percentile0_5      //  0.5% percentile
- percentile0_1      //  0.1% percentile
- firstTime          // Timestamp, in Unix milliseconds, of the first sample
- lastTime           // Timestamp, in Unix milliseconds, of the last sample
- minValueTime       // Timestamp, in Unix milliseconds, of the first occurrence of minimum value
- maxValueTime       // Timestamp, in Unix milliseconds, of the first occurrence of maximum value

Fields that cannot be calculated are set to NaN.

`db_multi_statistics`

db_multi_statistics(string interval, [ string metric, [string entity, [string tags | map tags]]]) number

Returns an object with the same statistics as the db_statistics function except delta, counter, wavg, wtavg, slope, and intercept. The statistics are calculated from all matching series unlike db_statistics which allows only one series.

avg() > 60 && db_multi_statistics('3 hour', 'temperature').max > 50

The tag value supports wildcards which can be used to select multiple series:

value > 60 && db_last('temperature', 'sensor-01', ['stage' : '*']) < 30

`db_statistic`

db_statistic(string function, string interval, [ string metric, [string entity, [string tags | map tags]]]) number

Returns the result of a statistical function applied to historical values loaded from the database. The function returns Double.NaN if no matching series are found or if no records are present within the selection interval.

The query must match only one series.

The function argument accepts a statistical function name such as avg applied to all values within the selection interval.

The interval argument is the duration of the selection interval specified in 'count units', for example, '1 hour'. The end of the selection interval is set to current time. The loaded interval is (current_timestamp - interval, current_timestamp].

If no other arguments are provided, the data is loaded for same metric, entity and tags as defined in the current window.

avg() > 60 && db_statistic('avg', '3 hour') > 30

To load data for a metric, other than the metric in the current window, specify metric name as a literal string or a field in the third argument.

avg() > 60 && db_statistic('avg', '3 hour', 'temperature') < 50

To load data for an entity, other than the entity in the current window, specify entity name as a literal string or a field in the fourth argument.

avg() > 60 && db_statistic('avg', '3 hour', 'temperature', 'sensor-01') < 50

To retrieve data for different series tags, specify them in the third argument:

'tag1=value1' to select series with tag1 equal to value1.
'tag1=*' to select series with tag1 containing any value.
Empty string '' for no series tags.
String containing one or multiple name=value pairs separated by comma: 'tag1=value1,tag2=value2'.
Key-value map: ['tag1':'value1','tag2':'value2']
The tags field representing the grouping tags of the current window.

db_last(string metric, string entity, string tags) number

db_last(string metric, string entity, map tags) number

Examples:

avg() > 60 && db_statistic('avg', '3 hour', 'temperature', 'sensor-01', 'stage=heating,unit=c') < 50

avg() > 60 && db_statistic('avg', '3 hour', 'temperature', 'sensor-01', ['stage':'heating', 'unit':'c']) < 50

avg() > 60 && db_statistic('avg', '3 hour', 'temperature', 'sensor-01', tags) < 50

`db_baseline`

db_baseline(string statFunc, int count, string period [, string windowLength]) number

Returns the averaged value (baseline) for the data stored in the rule window.

The algorithm of calculating the baseline is as follows:

Regularize historical data into periods, each of windowLength duration, with the last period ending with the timestamp of the last command.
Load samples for N (count) previous windows, excluding the last window. The distance between the previous windows is set with period.
Apply statistical function statFunc, such as avg or median, to the filtered values.

If the windowLength is not set explicitly, the length of the window equals Window Size for time-based windows. For count-based windows the windowLength is a required parameter.

Example:

// Last command time is 2019-01-20 10:08:00
// Period is 1 day and count is 5
// Windows are (10:03 - 10:08] on 01-15, ... , 01-19
// The result is 4.0

db_baseline('avg', 5, '1 day', '5 minute')

Window	Window Average
`2019-01-15 (10:03-10:08]`	`10.0`
`2019-01-16 (10:03-10:08]`	`0.0`
`2019-01-17 (10:03-10:08]`	`0.0`
`2019-01-18 (10:03-10:08]`	`10.0`
`2019-01-19 (10:03-10:08]`	`0.0`

ChartLab example

Series Match Examples

Both db_last and db_statistic functions search the database for matching series based on the specified metric/entity/tags filter and return a numeric value for the first matched series. If the series in the current window has tags which are not collected by the specified metric and entity, those tags are excluded from the filter.

`Tags : No Tags`

In the example below, the db_last('cpu_busy') function ignores the tags mount_point and file_system because the tags are not collected by the metric cpu_busy.

Current Window

metric = disk_used
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

Expression

db_last('cpu_busy') > 10

Search Filter

metric = cpu_busy
entity = nurswgvml007
tags   = [empty - no tags]

Matched Series

metric = cpu_busy
entity = nurswgvml007
tags   = no tags

`Same Tags`

In this example, the function db_last('disk_used_percent') uses the same series tags as in the current window because all of these tags are collected by the metric disk_used_percent.

Current Window

metric = disk_used
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

Expression

db_last('disk_used_percent') > 90

Search Filter

metric = disk_used_percent
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

Matched Series

metric = cpu_busy
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

`No Tags : Tags`

In this example, the function db_last('disk_used_percent') searches for a series with any tags configuration; the metric cpu_busy in the current window has no tags. If the search matches multiple series, the first series is returned. To better control which series is matched, use db_last('disk_used_percent', entity, 'mount_point=/') syntax.

Current Window

metric = cpu_busy
entity = nurswgvml007
tags   = [empty - no tags]

Expression

db_last('disk_used_percent') > 90

Search Filter

metric = disk_used_percent
entity = nurswgvml007
tags   = [empty - no tags]

Matched Series

metric = disk_used_percent
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

`Different Tags`

In this example, the function db_last('io_disk_percent_util') searches for the first series with any tags configuration; the metrics io_disk_percent_util and disk_used have different non-intersecting tag sets. If the search matches multiple series, the first series is returned. To better control which series is matched, use db_last('io_disk_percent_util', entity, 'device=sda') syntax.

Current Window

metric = disk_used_percent
entity = nurswgvml007
tags   = mount_point=/,file_system=/sda

Expression

db_last('io_disk_percent_util') > 90

Search Filter

metric = io_disk_percent_util
entity = nurswgvml007
tags   = [empty - no tags - because there are no intersecting tag names]

Matched Series

metric = io_disk_percent_util
entity = nurswgvml007
tags   = device=sda

# Database Series Functions

# Overview

# Reference

# db_last

# db_statistics

# db_multi_statistics

# db_statistic

# db_baseline

# Series Match Examples

# Tags : No Tags

# Same Tags

# No Tags : Tags

# Different Tags

Database Series Functions

Overview

Reference

`db_last`

`db_statistics`

`db_multi_statistics`

`db_statistic`

`db_baseline`

Series Match Examples

`Tags : No Tags`

`Same Tags`

`No Tags : Tags`

`Different Tags`