Workaround for a nested query in Kapactior TICKscript

425 views Asked by At

As far I know there is no possibility to execute nested query in Kapacitor TickScript, so I'm looking for some other way to achieve the same result as I have in InfluxQL query:

select count(*) from (SELECT sum("value")  FROM "measurement"."autogen"."consumption" WHERE (time > now() -5d -1h AND time <= now() - 5d) GROUP BY time(60m, 1ms), "param1", "param2", "param3")

The result of that query is a single point with one value, which contains total row numbers from nested query, for example 50.

I wrote something similar in tickscript:

   var cron = '0 59 * * * * *'

   var size_1 = batch
    |query('''
    SELECT sum(value) FROM "measurement"."autogen"."consumption"
    ''')
        .period(1h)
        .offset(5d - 1m - 1ms)
        .groupBy(time(60m, 1ms), 'param1', 'param2', 'param3')
        .fill(1)
        .cron(cron)
    |count('sum')
    |log()

   size_1
    |alert()
        .kafka()
        .kafkaTopic('influx')

But I don't get a single value in output, instead of that I have multiple points still grouped by that 3 parameters from query ('param1', 'param2', 'param3') and they are only counted non-unique set of params, fragment of kapacitor log():

2021-04-21T03:51:00.220+02:00
Kapacitor Point
3 Tags
param1: 2.8.0
param2: 0015474_7
param3: SUPPLEMENTARY
1 Fields
count: 2

2021-04-21T03:51:00.221+02:00
Kapacitor Point
3 Tags
param1: 2.8.0
param2: PW0001_1
param3: SUPPLEMENTARY
1 Fields
count: 2

etc.

How to get the same output with single count() resulut in kapacitor tickscript like in influxQL query?

0

There are 0 answers