Switch off displaying of "value" prefix in series lables

[dmak]

Dear community,
After upgrading from v7.5.2 to v8.4.5 I have noticed that the word “value” is displayed in from of series names on the chart. After some investigation I found out that is the name of the column of SQL query:

In other words, if I change AS value to AS v then the title changes to “v Bath humidity”.
Any idea how to rewrite the query and/or tick some option so that series label is displayed as e.g. “Bath humidity” and not “value Bath humidity”?
P.S. The same strange title is displayed in Query inspector:

Thanks in advance!

[yosiasz]

What happens if you do not alias it at all

[dmak]

Then the label reads max(cast(state AS float)) Bath humidity and chart line disappears:

[yosiasz]

Very strange. Please post sample csv data? I can try it locally. And what chart are you using? If you could post the json of it

[dmak]

This is standard “Time series” visualization (actually I have migrated from “Graph (old)” which had the same issue), here is its JSON:

  "id": 17,
  "gridPos": {
    "h": 9,
    "w": 12,
    "x": 12,
    "y": 10
  },
  "type": "timeseries",
  "title": "Humidity",
  "datasource": {
    "type": "mysql",
    "uid": "atZYCuJnv"
  },
  "pluginVersion": "8.4.6",
  "fieldConfig": {
    "defaults": {
      "custom": {
        "drawStyle": "line",
        "lineInterpolation": "smooth",
        "barAlignment": 0,
        "lineWidth": 1,
        "fillOpacity": 10,
        "gradientMode": "none",
        "spanNulls": true,
        "showPoints": "never",
        "pointSize": 5,
        "stacking": {
          "mode": "none",
          "group": "A"
        },
        "axisPlacement": "auto",
        "axisLabel": "",
        "scaleDistribution": {
          "type": "linear"
        },
        "hideFrom": {
          "tooltip": false,
          "viz": false,
          "legend": false
        },
        "thresholdsStyle": {
          "mode": "off"
        }
      },
      "color": {
        "mode": "palette-classic"
      },
      "mappings": [],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          },
          {
            "color": "red",
            "value": 80
          }
        ]
      },
      "links": [],
      "unit": "humidity"
    },
    "overrides": [
      {
        "matcher": {
          "id": "byName",
          "options": "Cellar humidity"
        },
        "properties": [
          {
            "id": "custom.fillOpacity",
            "value": 0
          }
        ]
      },
      {
        "__systemRef": "hideSeriesFrom",
        "matcher": {
          "id": "byNames",
          "options": {
            "mode": "exclude",
            "names": [
              "value Bath humidity"
            ],
            "prefix": "All except:",
            "readOnly": true
          }
        },
        "properties": [
          {
            "id": "custom.hideFrom",
            "value": {
              "legend": false,
              "tooltip": false,
              "viz": true
            }
          }
        ]
      }
    ]
  },
  "options": {
    "tooltip": {
      "mode": "multi",
      "sort": "none"
    },
    "legend": {
      "displayMode": "table",
      "placement": "bottom",
      "calcs": [
        "lastNotNull",
        "max",
        "min"
      ]
    }
  },
  "targets": [
    {
      "datasource": {
        "type": "mysql",
        "uid": "rtAYCuJnz"
      },
      "format": "time_series",
      "group": [],
      "metricColumn": "none",
      "rawQuery": true,
      "rawSql": "SELECT\n  max(unix_timestamp((last_updated))) AS time,\n  max(cast(state AS float)) AS value,\n  'Bath humidity' AS metrics\nFROM states\nWHERE $__timeFilter(last_updated)\nAND entity_id = 'sensor.bath_sensor_humidity'\nGROUP BY $__timeGroup(last_updated, $__interval)\nORDER BY $__timeGroup(last_updated, $__interval) ASC",
      "refId": "B",
      "select": [
        [
          {
            "params": [
              "event_id"
            ],
            "type": "column"
          }
        ]
      ],
      "table": "events",
      "timeColumn": "time_fired",
      "timeColumnType": "timestamp",
      "where": [
        {
          "name": "$__timeFilter",
          "params": [],
          "type": "macro"
        }
      ]
    },
    {
      "datasource": {
        "type": "mysql",
        "uid": "rtAYCuJnz"
      },
      "format": "time_series",
      "group": [],
      "metricColumn": "none",
      "rawQuery": true,
      "rawSql": "SELECT\n  max(unix_timestamp((last_updated))) AS time,\n  max(cast(state AS float)) AS value,\n  'Cellar humidity' AS metrics\nFROM states\nWHERE $__timeFilter(last_updated)\nAND entity_id = 'sensor.cellar_sensor_humidity'\nGROUP BY $__timeGroup(last_updated, $__interval)\nORDER BY $__timeGroup(last_updated, $__interval) ASC",
      "refId": "D",
      "select": [
        [
          {
            "params": [
              "event_id"
            ],
            "type": "column"
          }
        ]
      ],
      "table": "events",
      "timeColumn": "time_fired",
      "timeColumnType": "timestamp",
      "where": [
        {
          "name": "$__timeFilter",
          "params": [],
          "type": "macro"
        }
      ]
    },
    {
      "datasource": {
        "type": "mysql",
        "uid": "rtAYCuJnz"
      },
      "format": "time_series",
      "group": [],
      "metricColumn": "none",
      "rawQuery": true,
      "rawSql": "SELECT\n  max(unix_timestamp((last_updated))) AS time,\n  max(cast(state AS float)) AS value,\n  'Shed humidity' AS metrics\nFROM states\nWHERE $__timeFilter(last_updated)\nAND entity_id = 'sensor.shed_sensor_humidity'\nGROUP BY $__timeGroup(last_updated, $__interval)\nORDER BY $__timeGroup(last_updated, $__interval) ASC",
      "refId": "F",
      "select": [
        [
          {
            "params": [
              "event_id"
            ],
            "type": "column"
          }
        ]
      ],
      "table": "events",
      "timeColumn": "time_fired",
      "timeColumnType": "timestamp",
      "where": [
        {
          "name": "$__timeFilter",
          "params": [],
          "type": "macro"
        }
      ]
    }
  ]
}

CSV file:

"Time","value Bath humidity"
2022-04-22 15:23:37,53.2 %H
2022-04-22 15:30:39,53.5 %H
2022-04-22 15:35:32,53.5 %H
2022-04-22 16:06:36,53.2 %H
2022-04-22 16:22:54,52.6 %H
2022-04-22 16:34:09,52.6 %H
2022-04-22 17:22:00,52.2 %H

As you see, Grafana suggests the same label for CSV column as it is displayed in legend.

[yosiasz]

change

'Bath humidity' AS metrics

to

'Bath humidity' AS metric

or change it here

drop table states_sample;

create table states_sample(last_updated datetime, 
state decimal(3,1), entity_id varchar(150) )

insert into states_sample

select '2022-04-22 15:23:37',53.2, 'sensor.bath_sensor_humidity' union
select '2022-04-22 15:30:39',53.5, 'sensor.bath_sensor_humidity' union
select '2022-04-22 15:35:32',53.5, 'sensor.bath_sensor_humidity' union
select '2022-04-22 16:06:36',53.2, 'sensor.bath_sensor_humidity' union
select '2022-04-22 16:22:54',52.6, 'sensor.bath_sensor_humidity' union
select '2022-04-22 16:34:09',52.6, 'sensor.bath_sensor_humidity' union
select '2022-04-22 17:22:00',52.2 , 'sensor.bath_sensor_humidity'

[dmak]

Thanks, it helped! Nice catch! Really weird as the query used to work in v7, probably that bug was fixed

Looks to be another issue, but I’ve noticed that the color of the chart (yellow in my case) is different from one displayed in the legend (red). When I click on the legend (hide/show particular sequence), the yellow color is correctly shown everywhere:

Any ideas?

[yosiasz]

Yeah, I was squirming in my seat trying to figure that one out.

If you post the color thing as a new issue and I will take a look @dmak

[dmak]

Thanks, posted as new topic here.

[john2e54]

We just upgraded and had this same issue. I just aliased "AS ‘’ "… Super hard to read but I did an “AS” and two single quotes (‘’) and that worked.

Also, TRANSFORM will allow you to rename the columns.