Looking for help with a complex continuous query that writes zero points

InfluxDB 1 Kapacitor
,
1

Ahoy!

I am looking for some help with a continuous query. Here is the query, copy-pasted from my SHOW CONTINUOUS QUERIES output:

CREATE CONTINUOUS QUERY cq_snmp_interface_total ON telegraf RESAMPLE EVERY 1m FOR 1m BEGIN SELECT sum(bits) INTO telegraf.autogen.test_traffic FROM (SELECT non_negative_derivative(mean(ifHCInOctets), 1s) * 8 AS bits FROM telegraf.autogen.snmp_hcinterfaces WHERE (time > now() - 1m AND agent_host = '10.42.56.4' AND ifName =~ /^reth\d+$/) GROUP BY time(1m), *) GROUP BY time(1m), agent_host, device_model, device_type, env, host, instance, minion_id, primary_role, project, site, sysName END

Here is the same thing with line-breaks and indentation added for clarity:

CREATE CONTINUOUS QUERY cq_snmp_interface_total
  ON telegraf
  RESAMPLE EVERY 1m FOR 1m
  BEGIN
    SELECT sum(bits)
      INTO telegraf.autogen.test_traffic
      FROM (
        SELECT non_negative_derivative(mean(ifHCInOctets), 1s) * 8 AS bits
          FROM telegraf.autogen.snmp_hcinterfaces
          WHERE (time > now() - 1m AND agent_host = '10.42.56.4' AND ifName =~ /^reth\d+$/)
          GROUP BY time(1m), *
      )
    GROUP BY time(1m), agent_host, device_model, device_type, env, host, instance, minion_id, primary_role, project, site, sysName
  END

This query runs at the expected 1 minute interval. I can see it in the logs. It produces no error messages, but it also writes no data points. The telegraf.autogen.test_traffic measurement is not created.

Here is what the logs say:

Jun 26 07:43:00 influxdb-0 influxd[1721]: ts=2020-06-26T11:43:00.120238Z lvl=info msg="Continuous query execution (start)" log_id=0NAXIJ10000 service=continuous_querier trace_id=0Nd6r660000 op_name=continuous_querier_execute op_event=start
Jun 26 07:43:00 influxdb-0 influxd[1721]: ts=2020-06-26T11:43:00.120272Z lvl=info msg="Executing continuous query" log_id=0NAXIJ10000 service=continuous_querier trace_id=0Nd6r660000 op_name=continuous_querier_execute name=cq_snmp_interface_total db_instance=telegraf start=2020-06-26T11:42:00.000000Z end=2020-06-26T11:43:00.000000Z
Jun 26 07:43:00 influxdb-0 influxd[1721]: ts=2020-06-26T11:43:00.120402Z lvl=info msg="Executing query" log_id=0NAXIJ10000 service=query query="SELECT sum(bits) INTO telegraf.autogen.test_traffic FROM (SELECT non_negative_derivative(mean(ifHCInOctets), 1s) * 8 AS bits FROM telegraf.autogen.snmp_hcinterfaces WHERE (time > now() - 1m AND agent_host = '10.42.56.4' AND ifName =~ /^reth\\d+$/) GROUP BY time(1m), *) WHERE time >= '2020-06-26T11:42:00Z' AND time < '2020-06-26T11:43:00Z' GROUP BY time(1m), agent_host, device_model, device_type, env, host, instance, minion_id, primary_role, project, site, sysName"
Jun 26 07:43:00 influxdb-0 influxd[1721]: ts=2020-06-26T11:43:00.125790Z lvl=info msg="Finished continuous query" log_id=0NAXIJ10000 service=continuous_querier trace_id=0Nd6r660000 op_name=continuous_querier_execute name=cq_snmp_interface_total db_instance=telegraf written=0 start=2020-06-26T11:42:00.000000Z end=2020-06-26T11:43:00.000000Z duration=5ms
Jun 26 07:43:00 influxdb-0 influxd[1721]: ts=2020-06-26T11:43:00.125848Z lvl=info msg="Continuous query execution (end)" log_id=0NAXIJ10000 service=continuous_querier trace_id=0Nd6r660000 op_name=continuous_querier_execute op_event=end op_elapsed=5.621ms

If I run the exact same query directly without using a continuous query, then it works perfectly, and the correct data points are written into the test_traffic measurement

SELECT sum(bits) INTO telegraf.autogen.test_traffic FROM (SELECT non_negative_derivative(mean(ifHCInOctets), 1s) * 8 AS bits FROM telegraf.autogen.snmp_hcinterfaces WHERE (time > now() - 1m AND agent_host = '10.42.56.4' AND ifName =~ /^reth\d+$/) GROUP BY time(1m), *) GROUP BY time(1m), agent_host, device_model, device_type, env, host, instance, minion_id, primary_role, project, site, sysName

^ this works exactly as intended

Does anybody have any idea what I could be doing wrong?

2

Hello @James_Paige,
Welcome and Ahoy!
Sorry for the delay.
What time range are you running your “simple query” on? CQ won’t work on historical data. Only data that comes in after you start the CQ.

3

Hello, @Anaisdg !
The query is supposed to be running on new incoming data; time > now() - 1m
RESAMPLE EVERY 1m FOR 1m
so I would expect it to run once per minute over the most recent 1 minute of data (and the log messages are 1 minute apart)