# Query to get top N disks with high IOPS average over given period

**URL:** <https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091>\
**Category:** Fluxlang\
**Tags:** influxdb, time-series, query, flux\
**Created:** [November 9, 2023, 1:31pm UTC](https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091 "2023-11-09T13:31:30Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![chidori](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/chidori/32/14576_2.png) [@chidori](https://community.influxdata.com/u/chidori)\
**Post date:** [November 9, 2023, 1:31pm UTC](https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091/1 "2023-11-09T13:31:30Z")

</div>

Hi,  
I came up with below query that would display disk names which have high IOPS (average) for a given period. I want to get it reviewed here to see if there could be a room for improvement. I also wonder if this can somehow be made into one single query instead two.

My approach is use two queries  
query 1 - get the top N disk names with high IOPS average  
query 2 - make use of query 1 data as filter to display the high IOPS consuming disks.

```auto
REQ_FIELD = "reads"
// QUERY 1: query to collect top 5 disk names with high reads for a given time range
top_n_ds_names = from(bucket: "disk_metrics_bucket")
|> range(start: 2023-11-09T07:17:19.148Z, stop: 2023-11-09T13:17:19.148Z)
|> filter(fn: (r) => r._measurement == "disk_metrics" and
r["_field"] == REQ_FIELD and
contains(value: r["host"], set: ["foo_server"]))
|> drop(columns: ["host"])
|> highestAverage(n: 5, groupColumns: ["disk_name"])
|> keep(columns: ["disk_name"])
|> findColumn(fn: (key) => true, column: "disk_name")

// QUERY 2
// Show results only for those disks returned by QUERY 1
// Note: my use case is such that I need to use `derivative`
from(bucket: "disk_metrics_bucket")
|> range(start: 2023-11-09T07:17:19.148Z, stop: 2023-11-09T13:17:19.148Z)
|> filter(fn: (r) => r._measurement == "disk_metrics" and
r["_field"] == REQ_FIELD and
contains(value: r["host"], set: ["foo_server"]) and
contains(value: r["disk_name"], set: top_n_ds_names))
|> drop(columns: ["host"])
|> aggregateWindow(every: 1m, fn: last)
|> derivative(unit: 1s, nonNegative: true)
|> fill(column: "_value", value: 0.0)
|> yield(name: "non_negative_derivative")

```

P.S: I would be using this query inside a Grafana Dashboard.

Thank you!

---

<div class="post-metadata">

**Author:** ![chidori](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/chidori/32/14576_2.png) [@chidori](https://community.influxdata.com/u/chidori)\
**Post date:** [November 14, 2023, 12:02pm UTC](https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091/2 "2023-11-14T12:02:58Z")

</div>

Can someone kindly help me on this.

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [November 16, 2023, 8:02am UTC](https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091/3 "2023-11-16T08:02:27Z")

</div>

Hello @chidori,  
Sorry for the delay.  
That looks like a good query to me. I can’t think of a way to execute that logic in one query. Sorry to be the bearer of bad news in that way. It looks like your query is great.

---

<div class="post-metadata">

**Author:** ![chidori](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/chidori/32/14576_2.png) [@chidori](https://community.influxdata.com/u/chidori)\
**Post date:** [November 16, 2023, 11:48am UTC](https://community.influxdata.com/t/query-to-get-top-n-disks-with-high-iops-average-over-given-period/32091/4 "2023-11-16T11:48:08Z")

</div>

Thank you for the response. On another note, I wanted to add [timedMovingAverage()](https://docs.influxdata.com/flux/v0/stdlib/universe/timedmovingaverage/) to my query and the param `period` ( one of the function param for `timedMovingAverage()` I want it to be the window range I have chosen in grafana. Can you please suggest if this is doable?