Help displaying values / names in pie charts using influxDB2 and grafana

[raph1]

Hi all,

I’m struggling since I’ve updated to influxDB2 to build a pie chart.

problem is, I cannot figure out how to display the Db levels in my chart. I currently end-up with “value” for each part of the pie :

here is the full json for that chart :

{
  "datasource": {
    "uid": "wbROo3bVz",
    "type": "influxdb"
  },
  "fieldConfig": {
    "defaults": {
      "custom": {
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        }
      },
      "color": {
        "mode": "palette-classic"
      },
      "mappings": []
    },
    "overrides": []
  },
  "gridPos": {
    "h": 13,
    "w": 5,
    "x": 10,
    "y": 23
  },
  "id": 19,
  "options": {
    "reduceOptions": {
      "values": false,
      "calcs": [
        "lastNotNull"
      ],
      "fields": ""
    },
    "pieType": "pie",
    "tooltip": {
      "mode": "single",
      "sort": "none"
    },
    "legend": {
      "showLegend": true,
      "displayMode": "list",
      "placement": "bottom"
    },
    "displayLabels": [
      "name",
      "percent"
    ]
  },
  "pluginVersion": "9.4.3",
  "targets": [
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 80 and r[\"_value\"] < 85)\n  |> count()\n  |> map(fn: (r) => ({ name: \"80-85dB\", value: r._value }))\n",
      "refId": "A"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 86 and r[\"_value\"] < 90)\n  |> count()\n  |> map(fn: (r) => ({ name: \"86-90dB\", value: r._value }))",
      "refId": "B"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 91 and r[\"_value\"] < 95)\n  |> count()\n  |> map(fn: (r) => ({ name: \"91-95dB\", value: r._value }))",
      "refId": "C"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 96 and r[\"_value\"] < 100)\n  |> count()\n  |> map(fn: (r) => ({ name: \"96-100dB\", value: r._value }))",
      "refId": "D"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 101 and r[\"_value\"] < 105)\n  |> count()\n  |> map(fn: (r) => ({ name: \"100-105dB\", value: r._value }))",
      "refId": "E"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 106 and r[\"_value\"] < 110)\n  |> count()\n  |> map(fn: (r) => ({ name: \"106-110dB\", value: r._value }))",
      "refId": "F"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] >= 111 and r[\"_value\"] < 115)\n  |> count()\n  |> map(fn: (r) => ({ name: \"111-115dB\", value: r._value }))",
      "refId": "G"
    },
    {
      "datasource": {
        "type": "influxdb",
        "uid": "wbROo3bVz"
      },
      "hide": false,
      "query": "from(bucket: \"montefiore\")\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\n  |> filter(fn: (r) => r[\"_measurement\"] == \"noise_levels_montefiore_influxdb\")\n  |> filter(fn: (r) => r[\"location\"] == \"Montefiore\")\n  |> filter(fn: (r) => r[\"_field\"] == \"level\")\n  |> filter(fn: (r) => r[\"_value\"] > 115)\n  |> count()\n  |> map(fn: (r) => ({ name: \"+115dB\", value: r._value }))",
      "refId": "H"
    }
  ],
  "title": "% per noise level",
  "type": "piechart",
  "description": ""
}

in the past, I could simply enter a name in the “Alias” box of grafana, but now, I’m lost. Can someone let me know what to do to fix this ? I want to display what’s in the queries names (Eg: 80-85dB)

Thanks !

[raph1]

No one ? I can’t believe nobody creates pie charts since influxDB V2 was released

[grant2]

Try adding the following:

|> rename(columns: {_value: "something"})

[raph1]

unfortunately, now look how it’s called in the legend:

[grant2]

Can you try the Organize Fields transformation?

[raph1]

[grant2]

@raph1

How about an override?

[raph1]

it’s a good idea but :

it applies to all queries, therefore it doesn’t really help.

idea is really to display the noise level range for each part of the pie.

[grant2]

@grant2

|> rename(columns: {_value: “something”})

@raph1

If you go back and put the rename function back in and get this…

and then do the override (but choose the field name shown above, not “value”), then does it rename one slice of the pie?

I will admit this is not an elegant solution, but it should work.

[raph1]

problem is, the value is the same. it comes out of influxDB, and is called “value”.

I guess I have to act directly in influxDB, and move these different records to specific fields, like this:

• 80-95dB
• 86-90dB
• 91-95dB

etc.

the crazy thing is I’ll have to create specific database entries for these values. because grafana cannot simply alias them.

[grant2]

Can you share the output of your current query from Influx Data Explorer? The text / table, not the graph. I just need to see perhaps 5 or 6 lines / rows.

[raph1]

how do I export this ?

Only thing I can show is a screenshot for now I’m afraid :

[grant2]

[grant2]

If you are generating a pie chart, do you have just one row? I was expecting several. What if you change the time selector to something other than 1 minute?

[raph1]

yes The volume meter only reports one row. Idea is to sort the dB values.

I try to show the percentage of dBs between 80-86dB, 87-90dB, and so on.

here is the csv content :

#group,false,false,true,true,false,true,true,true
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,long,string,string,string
#default,_result,,,,,,,
,result,table,_start,_stop,_value,_field,_measurement,location
,,0,2022-03-19T00:52:11.053480947Z,2023-03-19T00:52:11.053480947Z,5259,level,noise_levels_montefiore_influxdb,Montefiore

when I was using influxQL I was easily displaying the name of each query by using aliases.

[grant2]

Let me work up something in my InfluxDB test system. I’ll post back once I have something.

What you want is definitely possible. I feel your pain re: Flux vs InfluxQL. Stay tuned…

[raph1]

yes, flux is really harder to use for lambda users. Everything was easier when using influxQL.

I mean, come on, this should be something basic that everyone could be doing out of the box.

Thing is, I can’t believe I’m the first guy building a dashboard with a pie chart…

[grant2]

I set up a random number generator between 80 and 95 to populate a value every 5 seconds, then changed it to 88 to 95. Data looks like this:

Using the above data, this query seems to work for the Grafana Pie Chart.

from(bucket: "RetroEncabulator")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "noise_levels_montefiore_influxdb")
  |> filter(fn: (r) => r["_field"] == "level")
  |> filter(fn: (r) => r["location"] == "Montefiore")
  |> map(fn: (r) => ({_time: r["_time"], value: r["_value"],
                      noise_level: if r["_value"] >= 80 and r["_value"] <= 85 then "80-85"
                             else if r["_value"] >= 86 and r["_value"] <= 90 then "86-90"
                             else if r["_value"] >= 91 and r["_value"] <= 95 then "91-95"
                             else "Other"}))
  |> group(columns: ["noise_level"])

Last 15 minutes:

Last 5 minutes:

[raph1]

Thank you so much !

I still can’t get rid of “value” but at least it displays what I need!

[grant2]

Hi @raph1

Glad it’s working. You can clean up the pie labels by using overrides.