Help Debugging Join in tick sctript

Robert_George · March 8, 2019, 8:30am

I’m trying to join two batch queries. However, after join the series is empties. Please help be debug this.

TICK script:
ID: join
Error:
Template:
Type: batch
Status: enabled
Executing: true
Created: 08 Mar 19 06:45 UTC
Modified: 08 Mar 19 08:13 UTC
LastEnabled: 08 Mar 19 08:13 UTC
Databases Retention Policies: [“telegraf”.“two_months”]
TICKscript:
dbrp “telegraf”.“two_months”

// The measurement to analyze
var measurement = ‘responsetimes’

// The amount of data to window at once
var window = 15m

var series = batch
|query(‘’’
SELECT count(timetaken) as count
FROM “telegraf”.two_months.responsetimes
WHERE “responsecode” != ‘10018’ AND “responsecode” != ‘10097’ AND “responsecode” != ‘10181’ AND “responsecode” != ‘10256’ AND “responsecode” != ‘10285’ AND “merchant” != ‘Sevasys’
‘’‘)
.period(window)
.groupBy(time(1m), ‘qcinstance’)
.cron(’*/1 * * * *')
.align()
.fill(0)
|log()
.prefix(‘series’)

var counts = batch
|query(‘’’
SELECT count(timetaken) as sum
FROM “telegraf”.two_months.responsetimes
WHERE “responsecode” != ‘10018’ AND “responsecode” != ‘10097’ AND “responsecode” != ‘10181’ AND “responsecode” != ‘10256’ AND “responsecode” != ‘10285’ AND “merchant” != ‘Sevasys’
‘’‘)
.period(window)
.groupBy(‘qcinstance’)
.cron(’*/1 * * * *')
.align()
.fill(0)
|log()
.prefix(‘counts’)

series
|join(counts)
.on(‘qcinstance’)
.fill(‘null’)
.tolerance(14m)
.as(‘counts’, ‘series’)
|log()
.prefix(‘join’)

DOT:
digraph join {
graph [throughput=“0.00 batches/s”];

query3 [avg_exec_time_ns=“2.683689867s” batches_queried=“52” errors=“0” points_queried=“52” working_cardinality=“0” ];
query3 → log4 [processed=“52”];

log4 [avg_exec_time_ns=“46.864µs” errors=“0” working_cardinality=“0” ];
log4 → join6 [processed=“52”];

query1 [avg_exec_time_ns=“0s” batches_queried=“52” errors=“0” points_queried=“780” working_cardinality=“0” ];
query1 → log2 [processed=“52”];

log2 [avg_exec_time_ns=“234.01µs” errors=“0” working_cardinality=“0” ];
log2 → join6 [processed=“52”];

join6 [avg_exec_time_ns=“6.863µs” errors=“0” working_cardinality=“0” ];
join6 → log7 [processed=“0”];

log7 [avg_exec_time_ns=“0s” errors=“0” working_cardinality=“0” ];
}

The logs:

ts=2019-03-08T08:14:00.674Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log4 prefix=counts name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_sum=11 time=2019-03-08T07:59:00Z

ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes 
group=qcinstance=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T07:59:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=4 time=2019-03-08T08:00:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:01:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:02:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:03:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:04:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:05:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:06:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=2 time=2019-03-08T08:07:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=1 time=2019-03-08T08:08:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:09:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=4 time=2019-03-08T08:10:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:11:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:12:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="batch point" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstan
ce=SCLP-FC tag_qcinstance=SCLP-FC field_count=0 time=2019-03-08T08:13:00Z
ts=2019-03-08T08:14:01.174Z lvl=info msg="end batch" service=kapacitor task_master=main task=join node=log2 prefix=series name=responsetimes group=qcinstance
=SCLP-FC tag_qcinstance=SCLP-FC time=2019-03-08T08:13:00Z

Here the series are not joining, even though the tag is matching and I have set a tolerance of 14 minutes

MarcV · March 11, 2019, 3:03pm

Hi , after these steps the join will succeed

replace the 2 .cron() by .every() ( because align() in combination with cron() does not work )
remove the .on() from the join node (joins are based on timestamp or a subset of a group by )
use the same .groupBy() in the two queries

best regards ,

Robert_George · March 12, 2019, 8:32am

Thanks, @MarcV. I figured out that if I use same .groupBy join works.

However, my intention of doing the join was different.

I’m doing anomaly detection on series generated by series batch query. In the alert I wanted to show the total number of requests I got which is available in counts query.

I thought if I do a join, I will get that value.

Any suggestions on how to get the total number of requests in the alert node?

MarcV · March 12, 2019, 9:46am

hi maybe this example can help ?

dbrp "telegraf"."autogen"

var window = 30s

var series = batch
|query('''SELECT usage_idle  FROM  "telegraf"."autogen".cpu ''')
.period(window)
.every(window)
.groupBy('cpu')
.align()
.alignGroup()
.fill(0)
|count('usage_idle')
 .as('countedvalue')
|alert()
.crit(lambda:"countedvalue" > 1 )
.message('the counted field for {{ index .Tags "cpu" }} is : {{ index .Fields "countedvalue" }}')

Topic		Replies	Views
Unable to join data in TICK script Kapacitor kapacitor	11	2731	October 17, 2019
Tick Script is not Inserting while using joins Store influxdb , kapacitor	0	736	October 16, 2019
Question: Join two batch nodes Kapacitor kapacitor	1	996	April 4, 2018
Join over a null query result Kapacitor kapacitor , chronograf	0	944	April 24, 2018
Result is empty when joining a batch with itself (shifted) Kapacitor kapacitor	0	671	January 21, 2018

Help Debugging Join in tick sctript

Related Topics