Flux query get top 5 of a tag

Systems
, , ,
1

Hello,

I’m trying to get the top 5 from a tag with a flux query
it’s a time series about performances during one day (granularity 1 minute)

Datas imported from this csv:
567819 lines and with 150 hdd_id ~

#datatype measurement,tag,tag,double,double,double,dateTime:number
HDD,server,hdd_id,Read_IOPS,Write_IOPS,Read_ms,time
HDD,srv1,hdd1,35,33,1,1671233940
HDD,srv1,hdd1,24,69,1,1671234000
HDD,srv1,hdd1,97,57,2,1671234060
HDD,srv1,hdd1,30,78,2,1671234120
HDD,srv1,hdd1,53,83,2,1671234180
HDD,srv1,hdd1,56,85,2,1671234240
HDD,srv1,hdd1,32,25,22,1671234300
HDD,srv1,hdd1,29,89,6,1671234360
HDD,srv1,hdd1,33,41,1,1671234420
HDD,srv1,hdd1,22,15,8,1671234480
HDD,srv1,hdd1,24,95,4,1671234540
…
HDD,srv1,hdd2,35,33,1,1671233940
HDD,srv1,hdd2,24,69,1,1671234000
HDD,srv1,hdd2,97,57,2,1671234060
HDD,srv1,hdd2,30,78,2,1671234120
HDD,srv1,hdd2,53,83,2,1671234180
HDD,srv1,hdd2,56,85,2,1671234240
HDD,srv1,hdd2,32,25,22,1671234300
HDD,srv1,hdd2,29,89,6,1671234360
HDD,srv1,hdd2,33,41,1,1671234420
HDD,srv1,hdd2,22,15,8,1671234480
HDD,srv1,hdd2,24,95,4,1671234540
…
HDD,srv1,hdd3,35,33,1,1671233940
HDD,srv1,hdd3,24,69,1,1671234000
HDD,srv1,hdd3,97,57,2,1671234060
HDD,srv1,hdd3,30,78,2,1671234120
HDD,srv1,hdd3,53,83,2,1671234180
HDD,srv1,hdd3,56,85,2,1671234240
HDD,srv1,hdd3,32,25,22,1671234300
HDD,srv1,hdd3,29,89,6,1671234360
HDD,srv1,hdd3,33,41,1,1671234420
HDD,srv1,hdd3,22,15,8,1671234480
HDD,srv1,hdd3,24,95,4,1671234540
…

My query without sucess:

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “HDD”)
|> filter(fn: (r) => r[“_field”] == “Read_IOPS” or r[“_field”] == “hdd_id”)
|> highestAverage(n:top, column: “Read_IOPS”, groupColumns: [“hdd_id”])

I would like to get the top 5 of hdd_id with the highiest Read_IOPS for example.

My csv/measurement is correctly build to perform this kind of query ?

Many thanks for any help.

2

Hello @indi,
What error are you getting?

3

Hello @Anaisdg,

I have no error, but the display is not that i want.
either the “_time” field is limited or the list of “hdd_id” is not reduced by the top x

Simply, from a graph with several tag from a field, i search to get the top X of the tag.

I think, i need to calculate the X tag with the max mean.
i tried with a new query witout success:

tophdd = from(bucket: v.bucket)
|>range(start: v.timeRangeStart, stop: v.timeRangeStop)
|>filter(fn: (r) => r._measurement == "hdd_id")
|>filter(fn: (r) => r.serial == "${Serial}")
|>filter(fn: (r) => r._field == "Read_IOPS")
|>highestMax(n:2,groupColumns: ["hdd_id"])
|>keep(columns: ["hdd_id" ])

|>from(bucket: v.bucket)
|>range(start: v.timeRangeStart, stop: v.timeRangeStop)
|>filter(fn: (r) => r._measurement == "hdd_id")
|>filter(fn: (r) => r.serial == "${Serial}")
|>filter(fn: (r) => r._field == "Read_IOPS")
|>filter(fn: (r) => r.hdd_id= =~ /"${tophdd}"/)
|>aggregateWindow(column: "_value", every: v.windowPeriod, fn: mean)

Is it possible to filter from a list?

i hope it’s more clear.

Many thanks,