Hi Nathaniel, Thats got it! Thanks for the help!
/me heads off to write lots and lots of new batch queries. Thanks, Peter On 25 October 2016 at 16:25, <[email protected]> wrote: > Peter, > > Your script is nearly correct there is just a small subtle issue causing > it to not quite work. Specifically the output of a query node is a batch > edge, Meaning that is the data is batched into sets of data. In your case > that batch of data contains one point, the mean value for the specified > time range. If you look closely at the two log lines before the join you > will see that each line has two times, tmax represents the max time for the > entire batch and the time of the point itself. Since join, joins on time > for batches this means tmax must match and the points times must also > match. In your case the tmax match as they are within the tolerance of 60s, > but the points times are 2w apart and as a result no longer match. The > resulting data from the join node is a batch without any points, and hence > no fields. > > So after that, what is the solution? Simply add a `last` operation to the > query nodes so that you select only the last point from each batch, in your > case the only point. This transforms the batch into a single "stream" point > with the time of tmax. Then when the points arrive at the join operation > they will properly match and the rest of your script should work. > > TL;DR do this: > > var last_minute = batch > |query('select mean(latency_avg) FROM "vdc"."default".latency') > .groupBy('source','destination') > .period(1m) > .every(1m) > // Use the last operation to extract the single mean point from the > result > |last('mean') > .as('mean') > |log() > .prefix('LATENCY_AVG:SHORT') > > var last_2weeks = batch > |query('select mean(latency_avg) FROM "vdc"."default".latency') > .groupBy('source','destination') > .period(2w) > .every(1m) > // Use the last operation to extract the single mean point from the > result > |last('mean') > .as('mean') > |log() > .prefix('LATENCY_AVG:LONG') > > last_2weeks > |join(last_minute) > .as('last_2weeks','last_minute') > .tolerance(60s) > |log() > .prefix('LATENCY_AVG:JOINED') > |eval(lambda: "last_minute.mean" / "last_2weeks.mean") > .as('ratio') > |log() > .prefix('LATENCY_AVG:END') > |alert().crit(lambda: "ratio" > 1.0) > .log('/tmp/latency.log') > > > > On Tuesday, October 25, 2016 at 5:29:57 AM UTC-6, Peter Farmer wrote: >> >> Hi, >> >> Been using influxdb for quite a while now, and have recently started >> using kapacitor to analysis data and generate alerts. All my simple alerts >> work perfectly, but I'm trying to do something slightly more complicated. >> I'm attempted to compare the average data from the last 60 seconds with the >> average data from the last 14 days, and then generate an alert if the last >> 60 seconds is significately greater than the last 14 days. Having looked at >> previous discussions on this subject I create the following tick script: >> >> var last_minute = batch >> |query('select mean(latency_avg) FROM "vdc"."default".latency') >> .groupBy('source','destination') >> .period(1m) >> .every(1m) >> |log() >> .prefix('LATENCY_AVG:SHORT') >> >> var last_2weeks = batch >> |query('select mean(latency_avg) FROM "vdc"."default".latency') >> .groupBy('source','destination') >> .period(2w) >> .every(1m) >> |log() >> .prefix('LATENCY_AVG:LONG') >> >> last_2weeks >> |join(last_minute) >> .as('last_2weeks','last_minute') >> .tolerance(60s) >> |log() >> .prefix('LATENCY_AVG:JOINED') >> |eval(lambda: "last_minute.mean" / "last_2weeks.mean") >> .as('ratio') >> |log() >> .prefix('LATENCY_AVG:END') >> |alert().crit(lambda: "ratio" > 1.0) >> .log('/tmp/latency.log') >> >> >> The vars are initially generated correctly: >> >> >> [latency_avg:log2] 2016/10/25 11:16:20 I! LATENCY_AVG:SHORT >> {"name":"latency","tmax":"2016-10-25T11:16:20.18402423Z"," >> group":"destination=zrh-jos-eu-col-1,source=sto-002-eu-col- >> 1","tags":{"destination":"zrh-jos-eu-col-1","source":"sto- >> 002-eu-col-1"},"points":[{"time":"2016-10-25T11:15:20.184 >> 02423Z","fields":{"mean":37.7},"tags":{"destination":"zrh- >> jos-eu-col-1","source":"sto-002-eu-col-1"}}]} >> [latency_avg:log4] 2016/10/25 11:16:25 I! LATENCY_AVG:LONG >> {"name":"latency","tmax":"2016-10-25T11:16:20.184029496Z"," >> group":"destination=zrh-jos-eu-col-1,source=sto-002-eu- >> col-1","tags":{"destination":"zrh-jos-eu-col-1","source":" >> sto-002-eu-col-1"},"points":[{"time":"2016-10-11T11:16:20.18 >> 4029496Z","fields":{"mean":37.731245818821975},"tags":{"dest >> ination":"zrh-jos-eu-col-1","source":"sto-002-eu-col-1"}}]} >> >> >> But once the join happens, there is are no fields in the data: >> >> >> [latency_avg:log7] 2016/10/25 11:16:25 I! LATENCY_AVG:JOINED >> {"name":"latency","tmax":"2016-10-25T11:16:00Z","group":" >> destination=zrh-jos-eu-col-1,source=sto-002-eu-col-1","tags" >> :{"destination":"zrh-jos-eu-col-1","source":"sto-002-eu-col-1"}} >> >> [latency_avg:log9] 2016/10/25 11:16:25 I! LATENCY_AVG:END >> {"name":"latency","tmax":"2016-10-25T11:16:00Z","group":" >> destination=zrh-jos-eu-col-1,source=sto-002-eu-col-1","tags" >> :{"destination":"zrh-jos-eu-col-1","source":"sto-002-eu-col-1"}} >> >> >> I'm pretty sure I'm doing something wrong here, so any pointers would be >> great. >> >> >> Thanks, >> >> Peter >> >> -- > Remember to include the version number! > --- > You received this message because you are subscribed to the Google Groups > "InfluxData" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/influxdb. > To view this discussion on the web visit https://groups.google.com/d/ > msgid/influxdb/73ae4243-eae0-4872-a5bb-ce667e97ce0e%40googlegroups.com > <https://groups.google.com/d/msgid/influxdb/73ae4243-eae0-4872-a5bb-ce667e97ce0e%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- Remember to include the version number! --- You received this message because you are subscribed to the Google Groups "InfluxData" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/influxdb. To view this discussion on the web visit https://groups.google.com/d/msgid/influxdb/CAP2GLJ%2BVVjsfk5g3a-aaCLEQyKECoUrYfAAOD__EyWTtqD%2B9vA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
