Downsampling - TICKscript, all measurement data (wildcard)

Store
1

Hi there,

I record several data points (> 20) using various measuring systems and save them in an InfluxDB. Each date point is a time series with different time stamps and measured values. I record the measured values every second and now I would like to downsample all to 5 minutes. I found the following TICKscript example:

stream
    |from()
        .database('telegraf')
        .measurement('cpu')
        .groupBy(*)
    |window()
        .period(5m)
        .every(5m)
        .align()
    |mean('usage_idle')
        .as('usage_idle')
    |influxDBOut()
        .database('telegraf')
        .retentionPolicy('autogen')
        .measurement('mean_cpu_idle')
        .precision('s')

Here the mean is only formed for time series ‘usage_idle’.
What is an appropriate syntax to do this for all time series? mean (*)? Using wildcard …?

I read on the Internet that this is not possible. A task has to be created for each time series and each measurement, which I cannot quite believe.

What would be an alternative approach to downsampling all time series?

Many thanks and regards,
Hendrixon

2

A continuous query will do the trick.

You can create it in influxdb, the syntax is almost like the one of a normal query.

CREATE CONTINUOUS QUERY "downsample_xxx" ON "databasename"
BEGIN
  SELECT mean("A") AS "A", mean("B") AS B INTO "destination RP.table" FROM "RP.table" GROUP BY time(5m)
END

If you want to downsample the data you got so far you may want to have a look at the advanced syntax and run the query once on all the data instead of the default interval only (in this example 5m)

CREATE CONTINUOUS QUERY "downsample_xxx" ON "databasename"
RESAMPLE FOR 2w
BEGIN 
  SELECT mean("A") AS "A", mean("B") AS B INTO "destination RP.table" FROM "RP.table" GROUP BY time(5m)
END

if you do so remember to let it run once and then drop and re-create it without the RESAMPLE FOR 2w otherwise it will compute the calculation on 2w of data every time it runs

3

Many thanks for your response. Unfortunately, this doesn’t really help me. You write on your query

mean (A) as A, mean (B) as B

But I need this for all date points without explicitly naming them. So for example:

SELECT mean (*) AS * INTO “destination RP.table” FROM “RP.table” GROUP BY time (5m)

Something like this is possible when using a wildcard. I want to avoid entering every name of the data point.

Thank you.

4

You can use the wildcard in an aggregate function, the output will have a the following name <function>_<field>

ie: I have the fields A,B,C.
if I run the query:
SELECT mean(*),max(*) FROM ___  GROUP BY *, time(5m)
The output will be:
mean_A, mean_B, mean_C, max_A, max_B, max_C
5

Okay, I’m going to test it today, give you feedback.