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.
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!
