diff options
Diffstat (limited to 'web/api/formatters/csv')
| -rw-r--r-- | web/api/formatters/csv/Makefile.am | 8 | ||||
| -rw-r--r-- | web/api/formatters/csv/README.md | 144 | ||||
| -rw-r--r-- | web/api/formatters/csv/csv.c | 142 | ||||
| -rw-r--r-- | web/api/formatters/csv/csv.h | 12 |
4 files changed, 306 insertions, 0 deletions
diff --git a/web/api/formatters/csv/Makefile.am b/web/api/formatters/csv/Makefile.am new file mode 100644 index 0000000..161784b --- /dev/null +++ b/web/api/formatters/csv/Makefile.am @@ -0,0 +1,8 @@ +# SPDX-License-Identifier: GPL-3.0-or-later + +AUTOMAKE_OPTIONS = subdir-objects +MAINTAINERCLEANFILES = $(srcdir)/Makefile.in + +dist_noinst_DATA = \ + README.md \ + $(NULL) diff --git a/web/api/formatters/csv/README.md b/web/api/formatters/csv/README.md new file mode 100644 index 0000000..df7c11e --- /dev/null +++ b/web/api/formatters/csv/README.md @@ -0,0 +1,144 @@ +<!-- +title: "CSV formatter" +custom_edit_url: https://github.com/netdata/netdata/edit/master/web/api/formatters/csv/README.md +--> + +# CSV formatter + +The CSV formatter presents [results of database queries](/web/api/queries/README.md) in the following formats: + +| format|content type|description| +| :----:|:----------:|:----------| +| `csv`|text/plain|a text table, comma separated, with a header line (dimension names) and `\r\n` at the end of the lines| +| `csvjsonarray`|application/json|a JSON array, with each row as another array (the first row has the dimension names)| +| `tsv`|text/plain|like `csv` but TAB is used instead of comma to separate values (MS Excel flavor)| +| `html`|text/html|an html table| +| `markdown`|text/plain|markdown table| + +In all formats the date and time is the first column. + +The CSV formatter respects the following API `&options=`: + +| option|supported|description| +|:----:|:-------:|:----------| +| `nonzero`|yes|to return only the dimensions that have at least a non-zero value| +| `flip`|yes|to return the rows older to newer (the default is newer to older)| +| `seconds`|yes|to return the date and time in unix timestamp| +| `ms`|yes|to return the date and time in unit timestamp as milliseconds| +| `percent`|yes|to replace all values with their percentage over the row total| +| `abs`|yes|to turn all values positive| +| `null2zero`|yes|to replace gaps with zeros (the default prints the string `null`| + +## Examples + +Get the system total bandwidth for all physical network interfaces, over the last hour, +in 6 rows (one for every 10 minutes), in `csv` format: + +Netdata always returns bandwidth in `kilobits`. + +```bash +# curl -Ss 'https://registry.my-netdata.io/api/v1/data?chart=system.net&format=csv&after=-3600&group=sum&points=6&options=abs' +time,received,sent +2018-10-26 23:50:00,90214.67847,215137.79762 +2018-10-26 23:40:00,90126.32286,238587.57522 +2018-10-26 23:30:00,86061.22688,213389.23526 +2018-10-26 23:20:00,85590.75164,206129.01608 +2018-10-26 23:10:00,83163.30691,194311.77384 +2018-10-26 23:00:00,85167.29657,197538.07773 +``` + +--- + +Get the max RAM used by the SQL server and any cron jobs, over the last hour, in 2 rows (one for every 30 +minutes), in `tsv` format, and format the date and time as unix timestamp: + +Netdata always returns memory in `MB`. + +```bash +# curl -Ss 'https://registry.my-netdata.io/api/v1/data?chart=apps.mem&format=tsv&after=-3600&group=max&points=2&options=nonzero,seconds&dimensions=sql,cron' +time sql cron +1540598400 61.95703 0.25 +1540596600 61.95703 0.25 +``` + +--- + +Get an HTML table of the last 4 values (4 seconds) of system CPU utilization: + +Netdata always returns CPU utilization as `%`. + +```bash +# curl -Ss 'https://registry.my-netdata.io/api/v1/data?chart=system.cpu&format=html&after=-4&options=nonzero' +<html> +<center> +<table border="0" cellpadding="5" cellspacing="5"> +<tr><td>time</td><td>softirq</td><td>user</td><td>system</td></tr> +<tr><td>2018-10-27 00:16:07</td><td>0.25</td><td>1</td><td>0.75</td></tr> +<tr><td>2018-10-27 00:16:06</td><td>0</td><td>1.0025063</td><td>0.5012531</td></tr> +<tr><td>2018-10-27 00:16:05</td><td>0</td><td>1</td><td>0.75</td></tr> +<tr><td>2018-10-27 00:16:04</td><td>0</td><td>1.0025063</td><td>0.7518797</td></tr> +</table> +</center> +</html> +``` + +This is how it looks when rendered by a web browser: + + + +--- + +Get a JSON array with the average bandwidth rate of the mysql server, over the last hour, in 6 values +(one every 10 minutes), and return the date and time in milliseconds: + +Netdata always returns bandwidth rates in `kilobits/s`. + +```bash +# curl -Ss 'https://registry.my-netdata.io/api/v1/data?chart=mysql_local.net&format=csvjsonarray&after=-3600&points=6&group=average&options=abs,ms' +[ +["time","in","out"], +[1540599600000,0.7499986,120.2810185], +[1540599000000,0.7500019,120.2815509], +[1540598400000,0.7499999,120.2812319], +[1540597800000,0.7500044,120.2819634], +[1540597200000,0.7499968,120.2807337], +[1540596600000,0.7499988,120.2810527] +] +``` + +--- + +Get the number of processes started per minute, for the last 10 minutes, in `markdown` format: + +```bash +# curl -Ss 'https://registry.my-netdata.io/api/v1/data?chart=system.forks&format=markdown&after=-600&points=10&group=sum' +time | started +:---: |:---: +2018-10-27 03:52:00| 245.1706149 +2018-10-27 03:51:00| 152.6654636 +2018-10-27 03:50:00| 163.1755789 +2018-10-27 03:49:00| 176.1574766 +2018-10-27 03:48:00| 178.0137076 +2018-10-27 03:47:00| 183.8306543 +2018-10-27 03:46:00| 264.1635621 +2018-10-27 03:45:00| 205.001551 +2018-10-27 03:44:00| 7026.9852167 +2018-10-27 03:43:00| 205.9904794 +``` + +And this is how it looks when formatted: + +| time | started | +|:--:|:-----:| +| 2018-10-27 03:52:00 | 245.1706149 | +| 2018-10-27 03:51:00 | 152.6654636 | +| 2018-10-27 03:50:00 | 163.1755789 | +| 2018-10-27 03:49:00 | 176.1574766 | +| 2018-10-27 03:48:00 | 178.0137076 | +| 2018-10-27 03:47:00 | 183.8306543 | +| 2018-10-27 03:46:00 | 264.1635621 | +| 2018-10-27 03:45:00 | 205.001551 | +| 2018-10-27 03:44:00 | 7026.9852167 | +| 2018-10-27 03:43:00 | 205.9904794 | + + diff --git a/web/api/formatters/csv/csv.c b/web/api/formatters/csv/csv.c new file mode 100644 index 0000000..603a171 --- /dev/null +++ b/web/api/formatters/csv/csv.c @@ -0,0 +1,142 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +#include "libnetdata/libnetdata.h" +#include "csv.h" + +void rrdr2csv(RRDR *r, BUFFER *wb, uint32_t format, RRDR_OPTIONS options, const char *startline, const char *separator, const char *endline, const char *betweenlines) { + //info("RRD2CSV(): %s: BEGIN", r->st->id); + QUERY_TARGET *qt = r->internal.qt; + long c, i; + const long used = qt->query.used; + + // print the csv header + for(c = 0, i = 0; c < used ; c++) { + if(unlikely(r->od[c] & RRDR_DIMENSION_HIDDEN)) continue; + if(unlikely((options & RRDR_OPTION_NONZERO) && !(r->od[c] & RRDR_DIMENSION_NONZERO))) continue; + + if(!i) { + buffer_strcat(wb, startline); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + buffer_strcat(wb, "time"); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + } + buffer_strcat(wb, separator); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + buffer_strcat(wb, string2str(qt->query.array[c].dimension.name)); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + i++; + } + buffer_strcat(wb, endline); + + if(format == DATASOURCE_CSV_MARKDOWN) { + // print the --- line after header + for(c = 0, i = 0; c < used ;c++) { + if(unlikely(r->od[c] & RRDR_DIMENSION_HIDDEN)) continue; + if(unlikely((options & RRDR_OPTION_NONZERO) && !(r->od[c] & RRDR_DIMENSION_NONZERO))) continue; + + if(!i) { + buffer_strcat(wb, startline); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + buffer_strcat(wb, ":---:"); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + } + buffer_strcat(wb, separator); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + buffer_strcat(wb, ":---:"); + if(options & RRDR_OPTION_LABEL_QUOTES) buffer_strcat(wb, "\""); + i++; + } + buffer_strcat(wb, endline); + } + + if(!i) { + // no dimensions present + return; + } + + long start = 0, end = rrdr_rows(r), step = 1; + if(!(options & RRDR_OPTION_REVERSED)) { + start = rrdr_rows(r) - 1; + end = -1; + step = -1; + } + + // for each line in the array + NETDATA_DOUBLE total = 1; + for(i = start; i != end ;i += step) { + NETDATA_DOUBLE *cn = &r->v[ i * r->d ]; + RRDR_VALUE_FLAGS *co = &r->o[ i * r->d ]; + + buffer_strcat(wb, betweenlines); + buffer_strcat(wb, startline); + + time_t now = r->t[i]; + + if((options & RRDR_OPTION_SECONDS) || (options & RRDR_OPTION_MILLISECONDS)) { + // print the timestamp of the line + buffer_rrd_value(wb, (NETDATA_DOUBLE)now); + // in ms + if(options & RRDR_OPTION_MILLISECONDS) buffer_strcat(wb, "000"); + } + else { + // generate the local date time + struct tm tmbuf, *tm = localtime_r(&now, &tmbuf); + if(!tm) { error("localtime() failed."); continue; } + buffer_date(wb, tm->tm_year + 1900, tm->tm_mon + 1, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec); + } + + int set_min_max = 0; + if(unlikely(options & RRDR_OPTION_PERCENTAGE)) { + total = 0; + for(c = 0; c < used ;c++) { + NETDATA_DOUBLE n = cn[c]; + + if(likely((options & RRDR_OPTION_ABSOLUTE) && n < 0)) + n = -n; + + total += n; + } + // prevent a division by zero + if(total == 0) total = 1; + set_min_max = 1; + } + + // for each dimension + for(c = 0; c < used ;c++) { + if(unlikely(r->od[c] & RRDR_DIMENSION_HIDDEN)) continue; + if(unlikely((options & RRDR_OPTION_NONZERO) && !(r->od[c] & RRDR_DIMENSION_NONZERO))) continue; + + buffer_strcat(wb, separator); + + NETDATA_DOUBLE n = cn[c]; + + if(co[c] & RRDR_VALUE_EMPTY) { + if(options & RRDR_OPTION_NULL2ZERO) + buffer_strcat(wb, "0"); + else + buffer_strcat(wb, "null"); + } + else { + if(unlikely((options & RRDR_OPTION_ABSOLUTE) && n < 0)) + n = -n; + + if(unlikely(options & RRDR_OPTION_PERCENTAGE)) { + n = n * 100 / total; + + if(unlikely(set_min_max)) { + r->min = r->max = n; + set_min_max = 0; + } + + if(n < r->min) r->min = n; + if(n > r->max) r->max = n; + } + + buffer_rrd_value(wb, n); + } + } + + buffer_strcat(wb, endline); + } + //info("RRD2CSV(): %s: END", r->st->id); +} diff --git a/web/api/formatters/csv/csv.h b/web/api/formatters/csv/csv.h new file mode 100644 index 0000000..666d4c6 --- /dev/null +++ b/web/api/formatters/csv/csv.h @@ -0,0 +1,12 @@ +// SPDX-License-Identifier: GPL-3.0-or-later + +#ifndef NETDATA_API_FORMATTER_CSV_H +#define NETDATA_API_FORMATTER_CSV_H + +#include "web/api/queries/rrdr.h" + +void rrdr2csv(RRDR *r, BUFFER *wb, uint32_t format, RRDR_OPTIONS options, const char *startline, const char *separator, const char *endline, const char *betweenlines); + +#include "../rrd2json.h" + +#endif //NETDATA_API_FORMATTER_CSV_H |