Using WHERE in a CQ

1

I have Telegraf collecting the standard set of host data, being written to DB metrics_host and RP one_week. The data looks like this:

14
141481×349

I’d like to downsample that data with the following CQ:

CREATE CONTINUOUS QUERY "downsample_cpu_v1" ON "metrics_host" BEGIN
  SELECT 100 - mean("usage_idle") AS "used_percent"
  INTO "metrics_host"."one_year"."cpu"
  FROM "metrics_host"."one_week"."cpu"
  WHERE "cpu" = 'cpu-total'
  GROUP BY time(10m), "host"
END

That is, I’d want to roll up only the cpu-total values per each host, and don’t care about the individual CPU’s. In fact, I very much don’t want them there, to save space in the one_year RP.

But the above CQ doesn’t work. It’s accepted by the DB, but doesn’t yield any results. Whereas this version does:

CREATE CONTINUOUS QUERY "downsample_cpu_v2" ON "metrics_host" BEGIN
  SELECT 100 - mean("usage_idle") AS "used_percent"
  INTO "metrics_host"."one_year"."cpu"
  FROM "metrics_host"."one_week"."cpu"
  GROUP BY time(10m), "host"
END

(it’s identical except it’s missing the WHERE clause)

But it’s not what I want, because it will combine the usage_idle from cpu0, cpu1, cpu2 and so on.

What am I doing wrong?

I’m on 1.2.

2

In the log, you should see something like this:

[I] 2017-09-28T15:22:40Z SELECT mean(value) INTO db0.autogen.cpu_total FROM db0.autogen.cpu WHERE cpu = 'cpu-total' AND time >= '2017-09-28T15:22:30Z' AND time < '2017-09-28T15:22:40Z' GROUP BY time(10s) service=query

Not the exact same text, but similar. It should say service=query at the end. When your continuous query runs, what is the output from the query engine for which query it is running?

3

Thanks for your swift reply!

This is what individual runs look like:

2017-09-29T05:39:00Z executing continuous query downsample_cpu_v1 (2017-09-29 05:36:00 +0000 UTC to 2017-09-29 05:39:00 +0000 UTC) service=continuous_querier
2017-09-29T05:39:00Z SELECT 100 - mean(usage_idle) AS used_percent INTO metrics_host.one_year.cpu FROM metrics_host.one_week.cpu WHERE cpu = 'cpu-total' AND time >= '2017-09-29T05:36:00Z' AND time < '2017-09-29T05:39:00Z' GROUP BY time(10m), host service=query
2017-09-29T05:39:00Z finished continuous query downsample_cpu_v1 (2017-09-29 05:36:00 +0000 UTC to 2017-09-29 05:39:00 +0000 UTC) in 5.177376ms service=continuous_querier

As far as I can tell, the query looks sensible…?

Edit: and running the query on the CLI works, too:

> SELECT 100 - mean(usage_idle) AS used_percent FROM metrics_host.one_week.cpu WHERE cpu = 'cpu-total' AND time >= '2017-09-29T05:36:00Z' AND time < '2017-09-29T05:39:00Z' GROUP BY time(10m), host

name: cpu
tags: host=host-a
time                used_percent
----                ------------
1506663360000000000 5.34959170632402

name: cpu
tags: host=host-bar
time                used_percent
----                ------------
1506663360000000000 3.2483566568117794

Edit 2: and it’s solved, and it was user error.

I was previously using InfluxDB over HTTP only, and as it turns out, there was a subtle error in my usage of curl --data-urlencode that produced no syntax errors but still an incorrect query. The query I posted above was from switching to the influx CLI, which didn’t mangle the query in the same way.

So thanks for pointing me to the correct direction! Lesson learned, sorry for wasting your time. :slight_smile:

4

I’m glad it’s fixed! Happy to help.