| 1 | # Database queries/lookup |
| 2 | |
| 3 | :::caution |
| 4 | |
| 5 | The `/api/v1/charts` endpoint is **deprecated** and **no longer supported**. It remains available in current releases but may be removed in a future version. For new integrations, use `/api/v3/contexts` instead to retrieve chart metadata and dimension information. |
| 6 | |
| 7 | ::: |
| 8 | |
| 9 | This document explains in detail the options available to retrieve data from the Netdata timeseries database in order to configure alerts, create badges or |
| 10 | create custom charts. |
| 11 | |
| 12 | The Netdata database can be queried with the `/api/v1/data` and `/api/v1/badge.svg` REST API methods. The database is also queried from the `lookup` line |
| 13 | in an [alert configuration](/src/health/REFERENCE.md). |
| 14 | |
| 15 | Every data query accepts the following parameters: |
| 16 | |
| 17 | |name|required|description| |
| 18 | |:--:|:------:|:----------| |
| 19 | |`chart`|yes|The chart to be queried.| |
| 20 | |`points`|no|The number of points to be returned. Netdata can reduce number of points by applying query grouping methods. If not given, the result will have the same granularity as the database (although this relates to `gtime`).| |
| 21 | |`before`|no|The absolute timestamp or the relative (to now) time the query should finish evaluating data. If not given, it defaults to the timestamp of the latest point in the database.| |
| 22 | |`after`|no|The absolute timestamp or the relative (to `before`) time the query should start evaluating data. if not given, it defaults to the timestamp of the oldest point in the database.| |
| 23 | |`group`|no|The grouping method to use when reducing the points the database has. If not given, it defaults to `average`. See [Grouping methods](#grouping-methods) for the full list, including `trimmed-mean`, `trimmed-median`, `percentile`, `countif`, and `extremes` variants.| |
| 24 | |`gtime`|no|A resampling period to change the units of the metrics (i.e. setting this to `60` will convert `per second` metrics to `per minute`. If not given it defaults to granularity of the database.| |
| 25 | |`options`|no|A bitmap of options that can affect the operation of the query. Only 2 options are used by the query engine: `unaligned` and `percentage`. All the other options are used by the output formatters. The default is to return aligned data.| |
| 26 | |`dimensions`|no|A simple pattern to filter the dimensions to be queried. The default is to return all the dimensions of the chart.| |
| 27 | |
| 28 | ## Operation |
| 29 | |
| 30 | The query engine works as follows (in this order): |
| 31 | |
| 32 | #### Time-frame |
| 33 | |
| 34 | `after` and `before` define a time-frame, accepting: |
| 35 | |
| 36 | - **absolute timestamps** (unix timestamps, i.e. seconds since epoch). |
| 37 | |
| 38 | - **relative timestamps**: |
| 39 | |
| 40 | `before` is relative to now and `after` is relative to `before`. |
| 41 | |
| 42 | Example: `before=-60&after=-60` evaluates to the time-frame from -120 up to -60 seconds in |
| 43 | the past, relative to the latest entry of the database of the chart. |
| 44 | |
| 45 | The engine verifies that the time-frame requested is available at the database: |
| 46 | |
| 47 | - If the requested time-frame overlaps with the database, the excess requested |
| 48 | will be truncated. |
| 49 | |
| 50 | - If the requested time-frame does not overlap with the database, the engine will |
| 51 | return an empty data set. |
| 52 | |
| 53 | At the end of this operation, `after` and `before` are absolute timestamps. |
| 54 | |
| 55 | #### Data grouping |
| 56 | |
| 57 | Database points grouping is applied when the caller requests a time-frame to be |
| 58 | expressed with fewer points, compared to what is available at the database. |
| 59 | |
| 60 | There are 2 uses that enable this feature: |
| 61 | |
| 62 | - The caller requests a specific number of `points` to be returned. |
| 63 | |
| 64 | For example, for a time-frame of 10 minutes, the database has 600 points (1/sec), |
| 65 | while the caller requested these 10 minutes to be expressed in 200 points. |
| 66 | |
| 67 | This feature is used by Netdata dashboards when you zoom-out the charts. |
| 68 | The dashboard is requesting the number of points the user's screen has. |
| 69 | This saves bandwidth and speeds up the browser (fewer points to evaluate for drawing the charts). |
| 70 | - The caller requests a **re-sampling** of the database, by setting `gtime` to any value |
| 71 | above the granularity of the chart. |
| 72 | |
| 73 | For example, the chart's units is `requests/sec` and caller wants `requests/min`. |
| 74 | |
| 75 | Using `points` and `gtime` the query engine tries to find a best fit for **database-points** |
| 76 | vs **result-points** (we call this ratio `group points`). It always tries to keep `group points` |
| 77 | an integer. Keep in mind the query engine may shift `after` if required. See also the [example](#example). |
| 78 | |
| 79 | #### Time-frame Alignment |
| 80 | |
| 81 | Alignment is a very important aspect of Netdata queries. Without it, the animated |
| 82 | charts on the dashboards would constantly [change shape](#example) during incremental updates. |
| 83 | |
| 84 | To provide consistent grouping through time, the query engine (by default) aligns |
| 85 | `after` and `before` to be a multiple of `group points`. |
| 86 | |
| 87 | For example, if `group points` is 60 and alignment is enabled, the engine will return |
| 88 | each point with durations XX:XX:00 - XX:XX:59, matching whole minutes. |
| 89 | |
| 90 | To disable alignment, pass `&options=unaligned` to the query. |
| 91 | |
| 92 | #### Query Execution |
| 93 | |
| 94 | To execute the query, the engine evaluates all dimensions of the chart, one after another. |
| 95 | |
| 96 | The engine does not evaluate dimensions that do not match the [simple pattern](/src/libnetdata/simple_pattern/README.md) |
| 97 | given at the `dimensions` parameter, except when `options=percentage` is given (this option |
| 98 | requires all the dimensions to be evaluated to find the percentage of each dimension vs to chart |
| 99 | total). |
| 100 | |
| 101 | For each dimension, it starts evaluating values starting at `after` (not inclusive) towards |
| 102 | `before` (inclusive). |
| 103 | |
| 104 | For each value it calls the **grouping method** given with the `&group=` query parameter |
| 105 | (the default is `average`). |
| 106 | |
| 107 | ## Grouping methods |
| 108 | |
| 109 | The following grouping methods are supported. These are given all the values in the time-frame |
| 110 | and they group the values every `group points`. |
| 111 | |
| 112 | -  finds the minimum value |
| 113 | -  finds the maximum value |
| 114 | -  finds the average value |
| 115 | -  adds all the values and returns the sum |
| 116 | -  sorts the values and returns the value in the middle of the list |
| 117 | -  finds the standard deviation of the values |
| 118 | -  finds the relative standard deviation (coefficient of variation) of the values |
| 119 | -  finds the exponential weighted moving average of the values |
| 120 | -  applies Holt-Winters double exponential smoothing |
| 121 | -  finds the difference of the last vs the first value |
| 122 | -  returns the percentage (0 to 100) of values matching a condition set via `group_options` (e.g., `&group=countif&group_options=>10`) |
| 123 | -  alias for `ses`; finds the exponential weighted moving average of the values |
| 124 | -  returns the maximum of positive values and the minimum of negative values; when both are present, returns the one with the greater absolute magnitude |
| 125 | -  finds the value at a specific percentile (defaults to the 95th percentile; accepts `group_options` to specify a different percentile; `percentile50` is equivalent to median) |
| 126 | -  finds the value at the 95th percentile |
| 127 | -  finds the value at the 99th percentile |
| 128 | -  finds the average after trimming outliers (defaults to trimming 5%; accepts `group_options` to specify a different percentage) |
| 129 | -  finds the median after trimming outliers (defaults to trimming 5%; accepts `group_options` to specify a different percentage) |
| 130 | |
| 131 | #### Percentile variants |
| 132 | |
| 133 | The following percentile methods are also available: `percentile25`, `percentile50` (equivalent to median), `percentile75`, `percentile80`, `percentile90`, `percentile95`, `percentile97`, `percentile98`, `percentile99`. The generic `percentile` method defaults to the 95th percentile and accepts `group_options` to specify a different percentile number. |
| 134 | |
| 135 | #### Trimmed variants |
| 136 | |
| 137 | The following trimmed-mean methods are available: `trimmed-mean1`, `trimmed-mean2`, `trimmed-mean3`, `trimmed-mean5`, `trimmed-mean10`, `trimmed-mean15`, `trimmed-mean20`, `trimmed-mean25`. The number indicates the percentage of values trimmed from each end. The generic `trimmed-mean` method defaults to trimming 5%. |
| 138 | |
| 139 | The following trimmed-median methods are available: `trimmed-median1`, `trimmed-median2`, `trimmed-median3`, `trimmed-median5`, `trimmed-median10`, `trimmed-median15`, `trimmed-median20`, `trimmed-median25`. |
| 140 | |
| 141 | #### group_options parameter |
| 142 | |
| 143 | Some grouping methods accept additional parameters via `group_options`: |
| 144 | - `countif`: A comparison operator followed by a value (e.g., `>100`, `<=50`, `!=0`, `<:5`, `>:10`) |
| 145 | - `percentile`: A number from 1-99 specifying the percentile |
| 146 | - `trimmed-mean` / `trimmed-median`: A number specifying the percentage of values to trim from each end |
| 147 | |
| 148 | The examples shown above show live information from the `received` traffic on the `eth0` interface of the global Netdata Registry. |
| 149 | Inspect any of the badges to see the parameters provided. You can directly issue the request to the Registry server's API yourself, e.g. by passing the following to get the value shown on the badge for the sum of the values within the period: |
| 150 | |
| 151 | ``` |
| 152 | https://registry.my-netdata.io/api/v1/data?chart=net.eth0&options=unaligned&dimensions=received&group=sum&units=kilobits&after=-60&label=sum&points=1 |
| 153 | ``` |
| 154 | |
| 155 | ## Further processing |
| 156 | |
| 157 | The result of the query engine is always a structure that has dimensions and values |
| 158 | for each dimension. |
| 159 | |
| 160 | Formatting modules are then used to convert this result in many different formats and return it |
| 161 | to the caller. |
| 162 | |
| 163 | ## Performance |
| 164 | |
| 165 | The query engine is highly optimized for speed. Most of its modules implement "online" |
| 166 | versions of the algorithms, requiring just one pass on the database values to produce |
| 167 | the result. |
| 168 | |
| 169 | ## Example |
| 170 | |
| 171 | When Netdata is reducing metrics, it tries to return always the same boundaries. So, if we want 10s averages, it will always return points starting at a `unix timestamp % 10 = 0`. |
| 172 | |
| 173 | Let's see why this is needed, by looking at the error case. |
| 174 | |
| 175 | Assume we have 5 points: |
| 176 | |
| 177 | |time|value| |
| 178 | |:--:|:---:| |
| 179 | |00:01|1| |
| 180 | |00:02|2| |
| 181 | |00:03|3| |
| 182 | |00:04|4| |
| 183 | |00:05|5| |
| 184 | |
| 185 | At 00:04 you ask for 2 points for 4 seconds in the past. So `group = 2`. Netdata would return: |
| 186 | |
| 187 | |point|time|value| |
| 188 | |:---:|:--:|:---:| |
| 189 | |1|00:01 - 00:02|1.5| |
| 190 | |2|00:03 - 00:04|3.5| |
| 191 | |
| 192 | A second later the chart is to be refreshed, and makes again the same request at 00:05. These are the points that would have been returned: |
| 193 | |
| 194 | |point|time|value| |
| 195 | |:---:|:--:|:---:| |
| 196 | |1|00:02 - 00:03|2.5| |
| 197 | |2|00:04 - 00:05|4.5| |
| 198 | |
| 199 | **Wait a moment!** The chart was shifted just one point and it changed value! Point 2 was 3.5 and when shifted to point 1 is 2.5! If you see this in a chart, it's a mess. The charts change shape constantly. |
| 200 | |
| 201 | For this reason, Netdata always aligns the data it returns to the `group`. |
| 202 | |
| 203 | When you request `points=1`, Netdata understands that you need 1 point for the whole database, so `group = 3600`. Then it tries to find the starting point which would be `timestamp % 3600 = 0` Within a database of 3600 seconds, there is one such point for sure. Then it tries to find the average of 3600 points. But, most probably it will not find 3600 of them (for just 1 out of 3600 seconds this query will return something). |
| 204 | |
| 205 | So, the proper way to query the database is to also set at least `after`. The following call will returns 1 point for the last complete 10-second duration (it starts at `timestamp % 10 = 0`): |
| 206 | |
| 207 | <http://netdata.firehol.org/api/v1/data?chart=system.cpu&points=1&after=-10&options=seconds> |
| 208 | |
| 209 | When you keep calling this URL, you will see that it returns one new value every 10 seconds, and the timestamp always ends with zero. Similarly, if you say `points=1&after=-5` it will always return timestamps ending with 0 or 5. |
| 210 | |
| 211 |