Trying to compare total mean to independent means

InfluxDB 1 Kapacitor
1

I am trying to compare means of the tagged means to the mean of all the combined values across a match so I can look for tags that stand out from the total mean. Perhaps this can be done in a single query, but i haven’t figured that out yet. These are the two queries I have so far.

var total_mean = batch
    |query('''
    	SELECT mean("load_avg_5m")
        FROM "heroku-prod"."autogen"."heroku_dyno_load"
        WHERE "source" =~ /web/
    ''')
        .period(1m)
        .every(1m)
        .groupBy(time(1m))
        .fill('null')

var independent_means = batch
    |query('''
    	SELECT mean("load_avg_5m")
        FROM "heroku-prod"."autogen"."heroku_dyno_load"
        WHERE "source" =~ /web/
    ''')
        .period(1m)
        .every(1m)
        .groupBy(time(1m), 'source')
        .fill('null')

I tried joining them

var means = total_mean
    |join(independent_means)
        .as('total', 'independent')
        .tolerance(1m)
        .fill(0.0)

But It doesn’t match them up, it produces records where either one side of the join is always filled with 0.

image
image1213×679 46.5 KB

Is there a single query that can do this? Or is there a better way to join them?
Thank You

2

Hello @William_DeLuca,
Welcome! I’m not sure how to do this with 1.x. What version are you using? I might be able to figure it out with 1.8 and Flux.
Otherwise @Emrys_Landivar do you have any suggestions here?
Thank you.

3

Hey @Anaisdg

We are using 1.8 and I do have Flux enabled, but not in use anywhere yet.

Thank you!

4

Hello @William_DeLuca,
Can you tell me a little bit more about your data and why you think you’re getting 0s?
Is data not aligning on the timestamp?
Can you try increasing the tolerance?

Otherwise the same query in Flux would look like:

total_mean = from(bucket: "heroku-prod")
  |> range(start: -1h, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "heroku_dyno_load")
  |> filter(fn: (r) => r["source"] =~ /web/)
  |> filter(fn: (r) => r["_field"] == "load_avg_5m")
  |> group() 
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: true)

independent_means = from(bucket: "heroku-prod")
  |> range(start: -1h, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "heroku_dyno_load")
  |> filter(fn: (r) => r["source"] =~ /web/)
  |> filter(fn: (r) => r["_field"] == "load_avg_5m")
  |> aggregateWindow(every: 1m, fn: mean, createEmpty: true)

join(tables: {total_mean: total_mean, independent_means : independent_means }, on: ["_time", "_start", "_stop", "_measurement"], method: "inner")
5

Thank you for this, but please excuse my ignorance; when I run this in chronograf, I am not seeing anything. I am not used to working with Flux, what am I doing wrong. I just copy and pasted it in to play with it.

6

You have to enable flux

7

It is enabled. I tested the first and second queries on there own and the ran fine.
image

image
image1185×299 38 KB

1 Like
8

@William_DeLuca,
Great, so it looks like it’s working?