Timeseries get top 10 results of sum query struggles

InfluxDB 1
1

I’m struggling to find a flux query to output what I want.

I have a bucket with tags for circuit, measurement ‘Bandwidth’ with fields of ‘Download’ and ‘Upload’.

I’m trying to get a graph that takes all the circuits, aggregates the last x minutes and then filter that to only show the top 10.

a quick sql query of what I would do, hopefully to provide clarity.
select Circuit, sum(Downloads) as Downloads from Bandwidths group by Circuit ordery by sum(Downloads) desc limit 10

Any help appreciated.
Thank you.

2

I find SQL hard to understand but I am pretty gut at Flux scripting.

I’ll give you an idea on how to do It whenever I had the chance.

1 Like
3

I’m the opposite, I find sql ‘native’ to understand and flux a difficult design. I asked chatgpt about 50 times to give me the type of output I want and it couldn’t do it either, so flux is AI defeating lol. Meanwhile it’ll nail the sql query first time every time.

4

Ok. lunchtime and already finished my sandwich lets figure this thin out.:

from(bucket: "bucket")
  |> range(start: -10m) //queries data 10 minutes relative to now
  |> filter(fn: (r) => r["_field"] == "Upload"  or r["_field"] == "Download" ) // select the fiedls to query
  |> aggregateWindow(every:1h,  fn: sum, createEmpty: false) // this will return a data point containign the sum of all values on windows of 1h since our time range is less than 1h then the result will be only one single datapoint containing the sum of the last 10 minutes.
  |> group(columns: ["_field"], mode:"by")  //this is to regroup . influx by default groups everything based on tags grouping by _fields we only get 2 tables containing the datapoints on all other tags .
  |> top(n:10, columns: ["_value"])   once we have only 1 table containing all we get only 
  |> yield ()

alternatively:

from(bucket: "bucket")
  |> range(start: -10m) //queries data 10 minutes relative to now
  |> filter(fn: (r) => r["_field"] == "Upload"  or r["_field"] == "Download" ) // select the fiedls to query
  |> aggregateWindow(every:1m,  fn: last, createEmpty: false) // change every to the minimun window you need
  |> cumulativeSum(columns: ["_value"])    |> last()     //this will return the cummulative sum and then we keep only last one
   //sum()                           // use this one if you dont care about retaining the timestamp
  |> group(columns: ["_field"], mode:"by")  //this is to regroup . influx by default groups everything based on tags grouping by _fields we only get 2 tables containing the datapoints on all other tags .
  |> top(n:10, columns: ["_value"])   once we have only 1 table containing all we get only 
  |> yield ()
5

Is it possible to sort the results of a formula? I have to present a graph with the 20 datastores that have the largest disk space occupation

the query is:

from(bucket: v.defaultBucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “vsphere_datastore_disk”)
|> filter(fn: (r) => r[“_field”] == “capacity_latest” or r[“_field”] == “used_latest”)
|> filter(fn: (r) => r[“source”] =~ /${datastore:regex}/)
|> group()
|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({ r with _value: float(v: r.used_latest) / float(v: r.capacity_latest) * 100.0 }))
|> group(columns: [“source”,“_field”])
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

6

sort() function | Flux 0.x Documentation (influxdata.com)

|> sort()

?

7

sorry but I’m not very good at flux language yet, can you explain to me how to get the results of the formulas from largest to smallest?

8

Add after the map.

|> sort ( column: “_value”, desc: true)