Unable to replicate top function in flux

I’m new to Flux query and have been trying to convert InfluxQL to FluxQL and having lot of issues or inaccurate data. Following query is in Influxql:

SELECT top("value","name",1) FROM (SELECT non_negative_derivative(last("weighted_io_time"), 1ms) AS "value" FROM "diskio" WHERE ("host" =~ /^$data_nodes_prod$/ AND "name" !~ /[0-9]/) AND $timeFilter GROUP BY time($__interval), "host", "name" fill(null)) GROUP BY "host"

For which I could write following fluxql but it does not give me the correct answer. Even I’ve tried with pivot but don’t understand where and how exactly this could be implemented.

  from(bucket: v.bucket)
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "diskio")
  |> filter(fn: (r) => r._field == "weighted_io_time")
  |> filter(fn: (r) => r["name"] == "/[0-9]/")
  |> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes:json}))
  |> derivative(unit: 1ms, nonNegative: true)
  |> top(n: 1)

Thanks in advance.

Hello @mr_sharma,
Welcome!
Thanks for your question. I would translate this:

SELECT top("value","name",1) FROM (SELECT non_negative_derivative(last("weighted_io_time"), 1ms) AS "value" FROM "diskio" WHERE ("host" =~ /^$data_nodes_prod$/ AND "name" !~ /[0-9]/) AND $timeFilter GROUP BY time($__interval), "host", "name" fill(null)) GROUP BY "host"

If you’re just looking at the top 1 value, I’d use the max() function instead. I’m confused how you’re getting a derivative after just getting the last() value.

I recommend splitting your subquery up incrementally and translating each bit to flux. In other words, try getting successfully filtering for your data first, then applying the last() function successfully, then the derivative, etc.

My Flux query would look like:

  from(bucket: v.bucket)
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "diskio")
  |> filter(fn: (r) => r._field == "weighted_io_time")
  |> filter(fn: (r) => r["host"] ==/^$data_nodes_prod$/ and r["name"] == "/[0-9]/")
  |> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes:json}))
  |> aggregateWindow(every: $__interval, fn: last)
  |> derivative(nonNegative: true)
  |> max() 

Does that help?

If that doesn’t help, or trying to go systematically doesn’t work for you, then try breaking your query up into incremental parts.

Hi @Anaisdg , Thank you for the response. I guess, I’m able to get closer to the answer. I executed following query:

  from(bucket: v.bucket)
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "diskio")
  |> filter(fn: (r) => r._field == "weighted_io_time")
  |> filter(fn: (r) => contains(value: r["host"], set: ${data_nodes_prod:json}))
  |> filter(fn: (r) => r["name"] == "sdc")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> derivative(unit: 1ms, nonNegative: true)
  |> max()

which gives me the correct data but not the correct labels. The labels looks like below but I need to show only two columns i.e. $host:$name above the bars.

Appreciate your help.

1 Like

Hello @mr_sharma,
Perhaps you can use the keep() function?

Hi @Anaisdg , Not sure but keep() function do not seems fit in this case. I’ve tried but its not working. If you look at the labels in the image above I just need to cut out two column names. I’d have done this very easily using sed/awk in linux, but here i’m unsure how to do that.

In older inlfuxdb query it was achieved using “Alias by” in following image:

I figured out the solution for desired label for the output. For one of my graph the lable was shown as:

{_resourcegroup_="rg-influx-enterpise_prod_westeu", partition="12", topic="to_metrics_shared"}

which I wanted to show as following:

rg-influx-enterpise_prod_westeu:to_metrics_shared:12

Solution:
Go the Transform tab, search for Rename by Regex.

Match: {resourcegroup=“(.)“, partition=”(.)”, topic=“(.*)”}

Replace: $1:$3:$2

Thanks.