How to get latest top N from sum of three fields grouped by two tags

[CArsten]

Hi,

we sample our batch scheduler hosts about 2-3 times per minute and record the number of running/idle/held jobs (3 fields) per user+id/scheduler (2 tags) via telegraf into influxdb.

For a grafana dashboard (e.g. BlendStat looks suitable), I would like to calculate

(a) the total number of jobs, i.e. running + idle + held jobs
(b) group these by ‘user+id’ and ‘scheduler’ tags
© and use only the last value recorded for these combos, provided they appeared within the last 5 minutes.

From all these, I would like to select the three results with the largest total number of jobs and display them next to each other on the dash board,e.g. like this:

+-------------+  +-------------+  +-------------+
|  user+id    |  |  user+id    |  |  user+id    |
|  scheduler  |  |  scheduler  |  |  scheduler  |
|   running   |  |   running   |  |   running   |
|     idle    |  |     idle    |  |     idle    |
|     held    |  |     held    |  |     held    |
+-------------+  +-------------+  +-------------+

(or if possible, even fancier with the time series graph for the past hour as a background).

However, I am still struggling to get my head around InfluxDB, e.g. I can use show tag values to gather the available ‘user+id’ and ‘scheduler’, but so far I was not able to use those to come up with a suitable query.

sample data looks like (condortag is user+id from above)

> select * from condor limit 2
name: condor
time                condortag        hold host    idle run
----                ---------        ---- ----    ---- ---
1580224190000000000 user1@test.test4 1    condor3    5 159
1580224190000000000 user2@test.test2 0    condor3 1140 335

Hopefully, I was able to get the base information over to you, is it possible to achieve this?

Cheers

Carsten

[CArsten]

Maybe it helps if I rephrased the problem a bit (hopefully I will use the correct technical terms).

Every measurement consists for exactly three fields and two tags. At the moment, I am only interested in the most recent measurements (say from within the past 10 minutes) and for every tag combination possible, only the most recent measurement.

For these I want to compute the derived field “total” which is just the addition of the three stored fields and SORT by this derived field.

And this is where I think my first problem lies, as so far I have only ever found the possibility to sort measurements by time but never by something else - is that true? Would flex/influxdb 2.0 help here?

[Anaisdg]

Hello @CArsten,
Sorry for the delay! Sometimes posts get lost .
I’m still having trouble understanding what it is that you’re trying to do. Are you trying to sort measurements based on the sum of their fields? If you’re trying to do math across measurements, you’ll need to enable flux and perform joins.

Otherwise, if you’re just looking to

• calculate the total number of jobs (running + idle + held jobs) for a single measurement and group by a tag, at the last time stamp, you could:

from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_user" or r["_field"] == "usage_steal" or r["_field"] == "usage_system" or r["_field"] == "usage_softirq")
  |> filter(fn: (r) => r["cpu"] == "cpu-total" or r["cpu"] == "cpu0")
  |> last()
  |> group(columns: ["host", "_measurement"], mode:"by")
  |> sum()

I’m using flux here with the docker plugin. To enable flux in 1.x follow this documentation.

Here is what the data transformation looks like: