Show result of query as stat visualisation

Grafana MySQL
,
1

Hello all,

I have a question about a Grafana dashboard im building.
I have a MySQL database with phonecalls in it and try to visualise some stats.
Im struggling on a simple thing and I hope someone can help me with this.
I just want tho show how often a phonecall was taking but I don’t be able to
bind this to the time picking of grafana.

This is my query:

SELECT
  begin AS "time",
  haendler AS "Eingehend"
FROM outbound
WHERE
  haendler = 1 AND
  hotline_id IN (2,3,4)
ORDER BY begin

begin is a datetime field in mysql which contains the time a phonecall was taking.
Im using a “Stat” Visualisation to just show me the number of calls:

My problem is that I don’t know how to configure the panel that way that it respects the time you can pick in grafana. Just show me the amount of calls in the last hours, 2 days, 1 week etc.
The amount in the picture below shows the total amount of calls. This is the only thing I get working. In the configuration of the Stats visualisation it is possible to choose “total”. This is okay but it didnt respects the time I was picking:

grafik

I hope somebody can help me with this.

2

Can you show us what your query produces using a Time Series visualization or a Table? Just wanting to see what it produces before trying to troubleshoot the Stat visualization.

3

Hi,

thanks for your answer. As you requested, here is a screenshot of a table view:

grafik
grafik1491×351 13.7 KB

And here as a timeseries:

grafik

4

In your query, between the WHERE statement and ORDER BY statemen, try inserting this:

GROUP BY time

5

Thank you for your answer. When I add the GROUP BY time and set in the Stats card under calculation Count or Total it still dont respect the time of the time picker. If I use Last it only returns 1. Which ist correct i think. Did you have another idea?

6

OK, so just to recap, even without the GROUP BY time clause, your query produces correct results (all the values below are correct?)…

image
image1501×340 14.3 KB

…but what you really want is a Stat Panel containing the TOTAL of the above for the given time picker range, i.e. 5 or 11 or something that totals up each of the above values over the time range?

If yes, then I think you can make the adjustments shown below in green. Try it and see what you get.

image
image1368×804 78.5 KB

7

Good morning grant2,

if I use GROUP BY time or not does not make a difference. In the table view I get the same results. As you said, I want a Stat Panel which shows me the total of entries from the query but for the time range from the the time picker. For example: Show me all calls from the last 3 days.

The adjustments in the Stats Panel works, but they did not respect the time picker range. I think the problem is in the query. When I use the table view it makes no difference if I chose the last hour or the last 30 days in the time packer. In the table I get the same results.

8

I thought the GROUP BY time might not be having any impact on the results, so thanks for clarifying.

Based on what you described, I definitely think your query is not referencing the time picker range. I am not familiar enough with MySQL datasource and how Grafana expects datetime fields to be referenced.

In my screenshot example, changing the time picker definitely changed the value being displayed (larger for more hours/days, smaller for minutes).

Just as a test, you could also try to build a similar query from scratch using another field from your data and see if you can get it to appear as a Stat Panel with the time picker working.