Bar Gauge : How to show Series name from a query's row value (MySQL)?

[slangloisde]

I’ve got this query :

SELECT hostmacro.value as macro, count(hostmacro.value) as compte FROM zabbix.hostmacro group by hostmacro.value;

I want to use the first column’s row values of the query (macro) as the series’s names for my Bar Gauge and use the 2nd column values (compte) as the value for each series. Right now, i’m not getting the expected result :

If i switch to a table view, i can see the 2 columns like this :

Anyone knows how to do this or if it’s possible with the new panel?

Thanks!

[torkel]

type $__cell_0 in the Field title template

[slangloisde]

Ah yes, it works now. I was a bit confused with the tooltip, as i tought i had to enter $__cell_{0}

Thanks!

[kei]

Hello! I cannot see that field in my query builder, could you please be so kind to post the full working query so I can edit my own? Thank you!

[scaarup]

Same problem here. I am not using the query builder. “Field title template” - where??

[tsavasci]

Hello,
What must I add to my query to show series? Is it…

SELECT * , $__series.name{Bar1}, FROM table WHERE No=10

or

SELECT * , $__field.name{Bar1}, FROM table WHERE No=10

or what? Thkns…

[laststupid]

Hi, here an exemple of query:

SELECT distinct(hostname) as hostname,count() as total FROM DB.Table
group by hostname order by count() desc

In the ‘Display’ tab on ‘Title’ field put : $__cell_0

It will make you bar title as the first field of your SQL query and the lenght of the bar will be the count(*)

[nico4]

Hi, i am trying to change the series name in a query very similar to this, but i cannot.
My query is like this:

SELECT sum(weight) FROM gitlab_issues where $timeFilter group by project

It works fine, but on the series title in the bar gauge it displays “gitlab_issues.sum {project: HR}”, when I would like it to show only the tag values, in this case “HR”, or at least removing the “gitlab_issues.sum” part.

Thanks.

[pooh]

Have you tried SELECT sum(weight) as HR FROM… ?

Antony.

[timjh]

Hi Torkel,

The other options as of _series_name and _field_name don’t appear to work. Am I missing something?

Thanks

[kestasli]

Could you share other configuration settings? I run very similar query with two columns returned, but for some reason gauge indicator shows each colum/row combination as separate gauge.

I have set “Title” field to $__cell_0

If I display same query as table, it shows 5 rows as expected:

Thanks!

[alexkolinko]

this is my query
select
sum(if(dik.ID = ‘1’ , dik.value, 0)) as ‘_a1’,
sum(if(dik.ID = ‘2’ , dik.value, 0)) as ‘_a2’,
sum(if(dik.ID = ‘3’ , dik.value, 0)) as ‘_a3’,
sum(if(dik.ID = ‘4’ , dik.value, 0)) as ‘_a4’,
sum(if(dik.ID = ‘5’ , dik.value, 0)) as ‘_a5’,
sum(if(dik.ID = ‘6’ , dik.value, 0)) as ‘_a6’,
sum(if(dik.ID = ‘7’ , dik.value, 0)) as ‘_a7’

from
(SELECT
$__timeGroup(Start,‘5m’,0) as “time”,
sum(VehicleActivities) as value,
ID
FROM request_info
WHERE
$__timeFilter(Start)
GROUP BY ID
ORDER BY Start) as dik

I get same to all bars in bar gauge.
Somebody can help

[capjairgarcia]

Using this macros could help us on the group by month?

[leynakarthik]

Please share your query after changed it as suggested.

[joesyverson]

Worked for me – this is what I was doing wrong.

I had trouble with the solution Torkel provided because (1) there did not seem to be any “Title” field for the bar gauge visualization and (2) I was mispelling the variable.

(1) In my version of Grafana, I had to modify the “Display Name” in the Field tab.

(2) I was only using one underscore. So, instead of “dollar underscore underscore cell underscore zero” ( $__cell_0 ), I was inputting “dollar underscore cell underscore zero” ( $_cell_0 )

The following is before and after. Instead of the metric “Count” appearing underneath each bar, the group by value appears (in this case, the “service.keyword”):

[jeffworne]

I’m using v7.0.6 and struggling with Bar Gauge.

My query is super simple.
SELECT mode,count(job) FROM info WHERE $__timeFilter("time") GROUP BY mode

This gives the expected plot but all items are named count. Then, I try to modify using the Display name option under Field, but using any name causes the plot to show “No Data”. If I call it “Count”, the plot says “m is undefined”. I’m at a loss here on how to get this working.

Edit:
I created a new panel and things seem to be working now. Another post suggests using ${__series.name} for Grafana v7+ but that didn’t work for me. I’m using ${__cell_0} and it works.

[mpaloni]

Hey, Little late for the conversation, but did you find a place, where these variables are defined? I’m trying to use $__cell_0 with the column name. This is not the first time I end up here with my searches

[jorgeazevedo]

A quick note for future travellers: $__cell_0 only works if Panel>Display>Field is set to Numeric Fields. If you pick just the field you want in that dropdown (like “compte” in the original post) you’ll get No Data instead of a graph.

Thanks @joesyverson for not only explaining the fixes but also including the before/after screenshots. I was able to compare my broken chart with yours and notice this little detail.

[fitzterra]

Thanks to @jorgeazevedo I finally got this working as expected.

Unfortunately the cell value I need for the name is a timesamp column from PostgreSQL and it is displayed as a Unix timestamp now, so I need to format the display value as a date.

Is this possible either by using some conversion function in the Display Name field (date($__cell_0) for example - I’m still new to Grafana, so apologies if this is a stupid options) or by using an override? I’ve tried various overrides, but can not find anything that will change only the display name.

[toff]

Hello everybody,
Apologies in advance for my grammatical syntax (I’m French).
I am working on Grafana v8.0.0 (Windows Platform).
I am not able to display correctly dates (months) with the Bar Gauge component.
I don’t understand clearly what happens.

However, all my data seems to be good (grouped by month)
See below when I switch on a “table view” :

All dates are the good ones.

See below, the displayed chart

I’ve tried many workaround using variables mentionned in this topic, without success.

Does anyone knows exactly how to proceed?

Thanks in advance for your valuable help!