How do I do last("file-nr")/last("file-max") with flux?

Fluxlang
,
1

I have a query in InfluxQL:

SELECT last(“file-nr”)/last(“file-max”) FROM “linux_sysctl_fs” WHERE “host” = ‘$host’ AND time > now()-5m

What would be the way to do it in flux?

2

I’ve found some guidelines in official docs, but it still not working because “file-max” is considered arithmetic operation of “file” and “max” variables. I am not able to escape in anyway. Any suggestions?

// SELECT last("file-nr")/last("file-max") FROM "linux_sysctl_fs" WHERE "host" = '$host' AND time > now()-5m

from(bucket: "telegraf")
  |> range(start: -5m)
  |> filter(fn: (r) =>
    r._measurement == "linux_sysctl_fs" and
    (r._field == "file-nr" or r._field == "file-max") and
    r.host == "${host}"
  )
  |> last()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r.file-nr / r.file-max }))
3

Hello @erkexzcx,
There are probably several ways to do this.
Here is one way:

import "influxdata/influxdb/schema"


from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_system" or r["_field"] == "usage_user")
  |> filter(fn: (r) => r["cpu"] == "cpu-total")
  |> last()
  |> group()
  |> schema.fieldsAsCols()
  |> map(fn: (r) => ({ r with _value: r.usage_system * r.usage_user}))

@scott do you have a better suggestion? Thanks!

4

@erkexzcx Your InfluxQL to Flux translation is spot on! Nice work! The only thing you missed was using bracket notation in your map call. Bracket notation lets you reference keys in a record that have special characters (in your case, -) in the key name:

from(bucket: "telegraf")
  |> range(start: -5m)
  |> filter(fn: (r) =>
    r._measurement == "linux_sysctl_fs" and
    r.host == "${host}" and 
    (r._field == "file-nr" or r._field == "file-max")
  )
  |> last()
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r["file-nr"] / r["file-max"] }))