Kapacitor and subqueries?

,
1

Hello,

I am trying to backfill some data using subquery with Kapacitor. The TICK script looks like this:

var test = batch
    |query('''
        SELECT SUM("mean") from (SELECT mean("value") FROM "collectd"."default".value"
        WHERE "host" =~ /^(..).[0-9]*.example.com/
        AND "type_instance" = 'default_interface_rx')
    ''')
        .period(1h)
        .every(1h)
        .groupBy(time(1h), *)
        .align()
    |delete()
        .tag('type')
    |influxDBOut()
        .database('history')
        .measurement('test')

After defining the script I am trying to record the batch of the past day with:
kapacitor record batch -task one_by_one -past 1d , but I am getting an error:

failed to parse InfluxQL query: found (, expected identifier at line 2, char 33

Looks like there is an error in the query itself? Any help would be appreciated.

Marijus

2

@Marijus Does that query return any data if you run it against your InfluxDB instance? It is giving me a parse error locally. Also can you please include your influxd and kapacitor versions?

3

Kapacitor does not currently (v1.2) support subqueries. Support will come in a later release.

4

@jackzampolin, sure, I am using:

InfluxDB 1.2.0-1
Kapacitor 1.2.0-1

With the query I can get the results:

SELECT SUM(“mean”) from (SELECT mean(“value”) FROM “collectd”.“default”.“value” WHERE “host” =~ /^(…).[0-9]*.example.com/ AND “type_instance” = ‘default_interface_rx’ AND time > now() - 1h GROUP BY time(1h))
name: value
time sum

0 6.187313478441984e+13

5

@nathaniel, thank You for the update!
Is there an easy way to sum the value in this case in other way?

6

Yes, Make the batch query the inner query and then perform the suming operation in Kapacitor with the |sum() node.

var test = batch
    |query('''
        SELECT mean("value") FROM "collectd"."default".value"
        WHERE "host" =~ /^(..).[0-9]*.example.com/
        AND "type_instance" = 'default_interface_rx'
    ''')
        .period(1h)
        .every(1h)
        .groupBy(time(1h), *)
        .align()
    |sum('mean')
    |delete()
        .tag('type')
    |influxDBOut()
        .database('history')
        .measurement('test')

Something along those lines

2 Likes
7

I tried it, but this solution doesn’t work for me either. What I want to achieve is to have the sum of default_interface_rx of all hosts that falls into this regular expression: /^(…).[0-9]*.example.com/. Instead, I am still getting

select * from test where time > now() - 2h
name: test
time                 host              sum                    type_instance
...
2017-03-10T09:00:00Z xxx.example.com   7.513598842337522e+13  default_interface_rx
2017-03-10T09:00:00Z xxx.example.com   7.747260959693897e+13  default_interface_rx
2017-03-10T09:00:00Z xxx.example.com   6.372869431513417e+11  default_interface_rx
2017-03-10T09:00:00Z xxx.example.com   2.4050401161566e+12    default_interface_rx
...

Looks like I am grouping incorrectly, or how the sum node works in this situation?

8

I am having a similar issue, i need to put a sum query to an anomalie ml model, and i cant execute the entire query, and i dont know how to do it…

the exactly query i need to parse and works me in influxdb is:

SELECT sum(brs) FROM(SELECT mean(“batch_requests”) as brs FROM nagios_mssqlserver.“autogen”.“batch_requests” WHERE time > now() - 6h AND (“host_name”=‘demunsql1702.production.ecom.techdata’ OR “host_name”=‘demunsql1707.production.ecom.techdata’ OR “host_name”=‘demunsql1708.production.ecom.techdata’ OR “host_name”=‘demunsql1709.production.ecom.techdata’ OR “host_name”=‘demunsql1710.production.ecom.techdata’) GROUP BY host_name, time(10m) ) WHERE time > now() - 6h GROUP BY time(10m)

Anybody can help me please? i dont know to much about batch tick scripts…

Thanks in advice