Kapacitor - Issues with Query + Join

#1

Hi,
I am seeing some weird behavior/issue that i am unable to explain.

  • I have a TickScript that runs 2 queries against influx table and joins the results that later get inserted into another table in influx. The queries have a “where clause”, run every(1m), period(30m), groupby(5m)

  • When i insert the data into the parent table in influx, my kapacitor script inserts the joined data correctly into the new table in influx. As i insert new data into my parent table, kapacitor does the calculations correctly for the first 30 minutes or so. After that as my script runs every minute, slowly the results start changing for the previously correct data.

  • Please see my tick script below.

  • To reproduce: insert the below data at random intervals every few minutes or so.
    meters,meterid=m1 value=1
    meters,meterid=m2 value=1

  • As you run a “select * on formula1” and you will notice that the data is correct for sometime and then slowly the data in the previously correct slots changes even though we have not inserted any more records into those slots.

  • I think the issue is being caused by the “period” when the query is issued to influx. Every time a query is issued the time range changes with the clock(ex: time netween 4:02 and 4:32, next time its time between 4:03 and 4:33) . This might be causing influx to return different results depending upon when the record was inserted even if it was in the correct 5m bucket.

  • Would appreciate some pointers.

dbrp “multi”.“autogen”

var series1 = batch
|query(’’‘SELECT mean(value) FROM “multi”.“autogen”."meters"
where meterid = ‘M1’ ‘’’)
.period(30m)
.every(1m)
.groupBy(time(5m) )
.align()
.fill(0)

var series2 = batch
|query(’’‘SELECT mean(value) FROM “multi”.“autogen”."meters"
where meterid = ‘M2’ ‘’’)
.period(30m)
.every(1m)
.groupBy(time(5m) )
.align()
.fill(0)

series1
|join(series2)
.as(‘M1’,‘M2’)
.fill(0)
|eval(lambda: float(“M1.mean”) * 5.0 + float(“M2.mean”) )
.as(‘value’)
.keep()
|influxDBOut()
.database(‘multi’)
.retentionPolicy(‘autogen’)
.measurement(‘formula1’)
.tag(‘seriesid’,‘Snew’)