'metric' column not pivoting in V10.1.1

Grafana MySQL
1

I currently have a visualization that works fine on grafana V9.1.2
The query has a ‘metric’ column, which pivots the Label column to be series labels.

SELECT $__time( Created ), Label AS metric, Value
FROM Data
WHERE $__timeFilter( Created )
AND Source = “enlighten”
ORDER BY Created ;

image
image1210×123 6.69 KB

When I run the same query in grafana V10.1.1, the data does not pivot. Table view in V9.1.2 shows the ‘metric’ value as a column label, while V10.1.1 shows the metric column as a column.

image

I’m not sure how to move forward on this. Any hints would be appreciated.

2

Welcome @dbags

I hate that change they did. Look into using group by matrix transformation

You will need to alias the datetime as time

Which visualization are you using?

3

Thanks heaps. It’s good to know I wasn’t missing something simple.

I’m using the ‘Time series’ visualization.

I recall reading somewhere that the ‘time_sec’ is deprecated and was curious as to why the $__time() macro uses it as the alias. I assume the $__time() macro deprecated too?

image
image835×71 9.14 KB

Now I am off to learn about ‘group by matrix transformation’. I suppose the upgrade will make me learn a few more things :slight_smile:

4

OK, that wasn’t hard. I now have a transformation.

image
image756×111 8.56 KB

but when I switch off table view, I get

image

This is the transformation

image
image727×83 5.78 KB

5

yuck one more transformation. I think this is messy there must be a better way. What database are you using, does it allow pivoting?

image
image1128×409 33.5 KB

7

Thanks yosiasz.
I am using mysql.
The query you propose has the metric values such as 50883055 hard coded into the query.
The problem with that is that I don’t know those metric values in advance.

I have tried your suggestion of removing all tranformations, and change the query Time Series format.

Good news is that has worked! I am now getting a chart. Not properly formatted yet but at least a chart.

Thanks heaps.

image
image910×817 67.3 KB

1 Like
8

Thats just sample data to reproduce your problem as we dont have access to your mysql :wink:

9

I had the same problem with a postgres data source. This saved my day!