Flux: use scalar result from sum() in another query

Fluxlang
, ,
1

I am writing a FluxQL query in Grafana v7.4.0 against InfluxDB v1.8.4.

Please first consider the following query snippet where I calculate the sum of differences across a set of tables (note that group() is used to combine all of the resultant tables into 1 table before computing the sum of all rows).

result_1 = …

min = result_1
|> min()
max = result_1
|> max()

//join(tables: {min: min, max: max}, on: ["_start", “_field”, “_start”, “host”, “_measurement”], method: “inner”)
sum_of_diffs = join(tables: {min: min, max: max}, on: ["_start", “host”], method: “inner”)
|> map(fn: (r) => ({ r with _value: r._value_max - r._value_min }))
|> group()
|> sum()

If I yield the result in a Grafana table, this results in a single ‘_value’ with an integer value. What I would like to do now is find a way to refer to the scalar value for this sum and use it in another query. I’ve tried using the following functions but they haven’t worked for me:

  1. findRecord like this:
    |> findRecord(idx: 0, fn: (key) => true)
    Result: “500 Internal Server Error: type error 25:6-25:16: undefined identifier “findRecord””

  2. getRecord like this:
    |> getRecord(idx: 0)
    Result: “500 Internal Server Error: type error 27:6-27:23: missing object properties (schema)”

  3. getColumn like this:
    |> getColumn(column: “_value”)
    “500 Internal Server Error: type error 29:6-29:33: missing object properties (schema)”

  4. I don’t think I have to use tableFind() because I only have 1 table in my output, right? I have tried using tableFind anyway and the result is the following error:
    “500 Internal Server Error: type error 30:6-30:27: missing object properties (schema)”

  5. If I just try to use the value as sum_of_diffs._value to refer to the result in another query then my influx query runs forever and doesn’t return anything.

Any help is appreciated - thanks!

2

Hello @Chris_King,
You still need to output a table in order to visualize it. You can take your scalar value and map it into a table like so:

data = from(bucket:"telegraf")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn:(r) => r._measurement == "cpu")
    |> group() 
    |> limit(n: 1)

r0 = data 
|> tableFind(fn: (key) => key._field == "usage_idle")
|> getRecord(idx: 0)
// Use record values
x = r0._value 

data 
  |> map(fn: (r) => ({ r with myvalue: x }))

Alternatively you can use findRecord() which is a combination of getRecord() and findTable():


data = from(bucket:"telegraf")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn:(r) => r._measurement == "cpu")
    |> group()
    |> limit(n: 1)

r0 = data 
  |> findRecord(fn: (key) => key.host == "usage_idle", idx: 0)
// Use record values
x = r0._value 

data 
  |> map(fn: (r) => ({ r with myvalue: x }))

Have you seen this already? If not, it could be useful.

Does that help?

Thanks

3

I think I’m missing something basic. I tried adding this block after my sum() statement above:

r0 = sum_of_diffs
|> tableFind(fn: (key) => key._field == “_value”)
|> getRecord(idx: 0)
// Use record values
x = r0._value

The result is: "message:“500 Internal Server Error: error calling function “getRecord”: error calling function “tableFind”: no table found”.

I suspect this makes sense because I think key._field == “_value” might not be quite right. However, I’m not sure what to use for key._field because all I have left from my original query is one column called “_value” so I’m not sure what the group key would be for this table? Is it even a table?

Anything else I can try?

4

You can set key to true if you’ve already filtered for the value you want

r0 = data 
  |> findRecord(fn: (key) => true, idx: 0)