Calculating Percent for values

  • What Grafana version and what operating system are you using?
    grafana-11.2.2-1.x86_64 on Centos 8

  • What are you trying to achieve?
    I’m trying to calculate the percentage of each ldevID for a specific MPU, instead of to have the IOPS values

  • How are you trying to achieve it?
    This is my query

total_iops = from(bucket: v.bucket) // calculate the sum of iops
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “${Serial}” and r.MPU == “MPU-010”)
|> keep(columns: [“_measurement”, “_time”, “_field”, “_value”])
|> aggregateWindow(every: 1m, fn: sum, createEmpty: true)
|> timedMovingAverage(every: $Interval, period: $Interval)
|> rename(columns: {_value: “Total_IOPS”})
|> drop(columns: [“_start”, “_end”, “_field”, “_stop”])

ldev_iops = from(bucket: v.bucket) // get all ldev iops
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “${Serial}” and r.MPU == “MPU-010”)
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> keep(columns: [“_measurement”, “_time”, “_field”, “_value”, “ldevID”])
|> rename(columns: {_value: “ldev_iops”})

union(tables: [ldev_iops, total_iops]) //perform the percentage of two tables
|> map(fn: (r) => ({ r with percentage: ( float(v: r.ldev_iops) / float(v: r.total_iops)) * 100 }))

  • What happened?
    i got this error message:

invalid: error @18:94-18:97: expected float but found int

  • What did you expect to happen?

  • Can you copy/paste the configuration(s) that you are having problems with?
    a sample of the influxdb

LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:0D,poolid=40,serial=1234 LDEV_IOPS=89 1733518860000000000
LDEV,HostGroup=SRV1,MPU=MPU-120,ldevID=00:03:00,poolid=40,serial=1234 LDEV_IOPS=89 1733518860000000000
LDEV,HostGroup=SRV2,MPU=MPU-010,ldevID=00:03:ED,poolid=40,serial=1234 LDEV_IOPS=161 1733515260000000000
LDEV,HostGroup=SRV2,MPU=MPU-010,ldevID=00:03:ED,poolid=40,serial=1234 LDEV_IOPS=124 1733515320000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:EF,poolid=40,serial=1234 LDEV_IOPS=137 1733517840000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:EF,poolid=40,serial=1234 LDEV_IOPS=133 1733517900000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:EF,poolid=40,serial=1234 LDEV_IOPS=113 1733517960000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F1,poolid=40,serial=1234 LDEV_IOPS=161 1733515260000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F1,poolid=40,serial=1234 LDEV_IOPS=123 1733515320000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F1,poolid=40,serial=1234 LDEV_IOPS=176 1733515380000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F5,poolid=40,serial=1234 LDEV_IOPS=190 1733517780000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F5,poolid=40,serial=1234 LDEV_IOPS=137 1733517840000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F5,poolid=40,serial=1234 LDEV_IOPS=132 1733517900000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F5,poolid=40,serial=1234 LDEV_IOPS=112 1733517960000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F7,poolid=40,serial=1234 LDEV_IOPS=161 1733515260000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F7,poolid=40,serial=1234 LDEV_IOPS=122 1733515320000000000
LDEV,HostGroup=SRV1,MPU=MPU-010,ldevID=00:03:F7,poolid=40,serial=1234 LDEV_IOPS=175 1733515380000000000

the original graphic:

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    invalid: error @18:94-18:97: expected float but found int

  • Did you follow any online instructions? If so, what is the URL?
    yes

Many thanks for help, i tried every things i know

1 Like

Hi @indi59

I believe the data type is expecting the constants to be in the same type (i.e. float). Try this (note that I have changed 100 to 100.0):
|> map(fn: (r) => ({ r with percentage: (float(v: r.ldev_iops) / float(v: r.total_iops)) * 100.0 }))

Hi @grant2

Many thanks, wast of time just for a comma :slight_smile:

but the percentage has no value :frowning:

I tried with others way but witjout success
I don’t understand what is wrong in the calculation %

total_iops = from(bucket: v.bucket) // calculate the sum of ldevID by time
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “${Serial}” and r.MPU == “MPU-010”)
|> keep(columns: [“_measurement”, “_time”, “_field”, “_value”])
|> aggregateWindow(every: 1m, fn: sum, createEmpty: true)
|> timedMovingAverage(every: $Interval, period: $Interval)
//|> rename(columns: {_value: “Total_IOPS”})
|> drop(columns: [“_start”, “_end”, “_field”, “_stop”])

ldev_iops = from(bucket: v.bucket) // ldev data
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “${Serial}” and r.MPU == “MPU-010”)
|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> keep(columns: [“_measurement”, “_time”, “_field”, “_value”, “ldevID”])
//|> rename(columns: {_value: “ldev_iops”})

percent = union(tables: [ldev_iops, total_iops]) //union the two tables
//|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({ r with percentage: ( float(v: r.ldev_iops) / float(v: r.total_iops)) * 100.0 }))
//|> map(fn: (r) => ({ r with percentage: r.ldev_iops / r.total_iops * 100.0 }))
|> drop(columns: [“_value”,“_field”])

percent

Hi @indi59

Did my suggestion resolve the error you were getting?

If yes, but you are now getting “No Value” can you try putting a yield() statement after the query below?

Samue issue, no more value :frowning:
no value for each minute

In Influx Data Explorer, can you post a snippet of the results you get from the total_iops and the ldev_iops queries? I presume these are two distinct tables (hence the union), but it would be good to see the results of each to try to identify why you are not getting any values.

I modified the query without no more success :frowning:

total_iops = from(bucket: “bucket-grafana”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “1234” and r.MPU == “MPU-010”)
|> group(columns: [“ldevID”])
|> sum()
|> drop(columns: [“ldevID”])

total_iops

table_result _valueno groupdouble _startgroupdateTime:RFC3339 _stopgroupdateTime:RFC3339
0 231 2024-11-22T10:53:48.165Z 2024-12-22T10:53:48.165Z
0 2942 2024-11-22T10:53:48.165Z 2024-12-22T10:53:48.165Z
0 6185 2024-11-22T10:53:48.165Z 2024-12-22T10:53:48.165Z
0 31875 2024-11-22T10:53:48.165Z 2024-12-22T10:53:48.165Z

ldev_iops = from(bucket: “bucket-grafana”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” and r.serial == “1234” and r.MPU == “MPU-010”)
//|> aggregateWindow(every: 1m, fn: mean, createEmpty: true)
|> drop(columns: [“HostGroup”,“MPU”,“serial”,“poolid”])

ldev_iops

table_result _measurementgroupstring _fieldgroupstring _valueno groupdouble _startgroupdateTime:RFC3339 _stopgroupdateTime:RFC3339 _timeno groupdateTime:RFC3339 ldevIDgroupstring
0 LDEV LDEV_IOPS 639 2024-11-22T11:06:07.063Z 2024-12-22T11:06:07.063Z 2024-12-06T04:23:00.000Z 0:03:01
0 LDEV LDEV_IOPS 256 2024-11-22T11:06:07.063Z 2024-12-22T11:06:07.063Z 2024-12-06T04:24:00.000Z 0:03:01
0 LDEV LDEV_IOPS 128 2024-11-22T11:06:07.063Z 2024-12-22T11:06:07.063Z 2024-12-06T04:25:00.000Z 0:03:01
0 LDEV LDEV_IOPS 56 2024-11-22T11:06:07.063Z 2024-12-22T11:06:07.063Z 2024-12-06T04:26:00.000Z 0:03:01

percent = union(tables: [ldev_iops, total_iops])
//|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> map(fn: (r) => ({ r with percentage: r.ldev_iops / r.total_iops * 100.0 }))
//|> drop(columns: [“_value”,“_field”])

percent

Hello,

I tried an other query i think more simply , but i get a error too:

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV”)
|> filter(fn: (r) => r._field == “LDEV_IOPS”)
|> group(columns: [“ldevID”])
|> sum()
|> map(fn: (r) => ({ r with _value: r._value / sum(r._value) * 100.0 }))

invalid: compilation failed: error @7:50-7:63: expected comma in property list, got DOT error @7:55-7:62: unexpected token for property key: DOT (.)

Can you try changing the above to this and see if the error persists?
|> map(fn: (r) => ({ r with _value: r._value / sum(v: r._value) * 100.0 }))

I tried this , but i get this error: invalid: error @8:66-8:84: found unexpected argument v (Expected one of tables or column) error @8:59-8:85: missing required argument tables error @8:59-8:85: expected float but found stream[A]

|> map(fn: (r) => ({ r with _value: float(v:r._value) / sum(v: float(v: r._value)) * 100.0 }))

One change at a time please. What error message does the above give?

With this map:

|> map(fn: (r) => ({ r with _value: r._value / sum(v: r._value) * 100.0 }))

invalid: error @9:56-9:64: found unexpected argument v (Expected one of tables or column) error @9:49-9:65: missing required argument tables error @9:38-9:65: stream[A] is not Divisible error @9:68-9:73: expected stream[A] but found float error @9:38-9:73: stream[A] is not Divisible

i’m out of idea :slight_smile:

Can you please post a small sample of the .csv data returned by this query?

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "LDEV")
|> filter(fn: (r) => r._field == "LDEV_IOPS")
|> group(columns: ["ldevID"])
|> sum()
|> yield(name: "test")

“_value {_start=”“2024-11-14 22:57:32.446 +0000 UTC”“, _stop=”“2024-11-15 23:10:06.585 +0000 UTC”“, ldevID=”“00:10:00"”}"
16686

“_value {_start=”“2024-11-14 22:57:32.446 +0000 UTC”“, _stop=”“2024-11-15 23:10:06.585 +0000 UTC”“, ldevID=”“00:10:01"”}"
1197410

etc… for a lot of ldevID

This is very hard to decipher. Can you run the query in Influx Data Explorer and post a screenshot showing the columns and a few rows of data?

Sorry, of course, please find:

Many thanks for your time :slight_smile:

Post it as useable text not an image

Now post a screenshot of what this query gives:

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "LDEV")
|> filter(fn: (r) => r._field == "LDEV_IOPS")
|> group(columns: ["ldevID"])
|> sum()
|> group()
|> yield(name: "test2")

here is:

with usable datas:

tabletest2 _valueno groupdouble _startno groupdateTime:RFC3339 _stopno groupdateTime:RFC3339 ldevIDno groupstring
0 16686 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:00
0 1197410 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:01
0 1002231 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:02
0 1138478 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:03
0 313267 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:04
0 15296 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:05
0 15295 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:06
0 111210 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:07
0 114777 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:08
0 193156 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 0:10:09
0 948671 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 00:10:0A
0 20200 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 00:10:0B
0 210 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 00:10:0C
0 208730 2024-11-13T14:24:30.000Z 2024-11-17T14:24:30.000Z 00:10:0D

Now try this:

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "LDEV")
|> filter(fn: (r) => r._field == "LDEV_IOPS")
|> group(columns: ["ldevID"])
|> sum()
|> group()
|> map(fn: (r) => ({ r with _value: r._value / sum(v: r._value) * 100.0 }))
|> yield(name: "test3")