Graph Display -Count MySQL

1

Hello all,

I’m trying to display a graph with my different COUNT MySQL.

See my dashboard with SingleStat. I want to get my Total Single Stat in a graph for the current day.

image
image1901×395 30.8 KB

Thanks in advance. :slight_smile:

2

Who can help me ? :frowning:

3

Not sure what the problem is? It sounds like a fairly simple SQL query?

4

I would like to display a graph according to the count of my request

5

image
image1877×821 31 KB

6

That query looks totally incorrect for time series data. Did you try to use the templated query or did you miss the help section? For the graph panel, you will want Time series data. Substitute the “time_column”, “value_column” and “series name column” with the names of your 3 columns.

image
image1559×824 96.4 KB

For Singlestat panel, change the Format as to Table table data and then the query should return one value (and use WHERE $__timeFilter(time_column) to get it to match the time period chosen in Grafana). For example:

image
image1739×635 49.8 KB

7

Ok, but I have just one point on my graph, i want get the date when the query is executed, and so get historic point graph

image
image1895×798 54.2 KB

8

Don’t really understand. If you want more data on your graph then increase the time range maybe? (I can’t see what time range you have chosen, it looks like last 6 hours? Try increasing it to 1 day or 1 week.)

9

I choose 12h, but now i have this error:

image
image743×495 15.5 KB

10

Do you have nulls in your when column? Grafana does not allow nulls for timestamps. Add a check to your where clause to eliminate nulls.

11

No i don’t have NULL in my when column.

12
  1. If you execute the generated query in a table panel (or in MySql Workbench), does the result look right?
  2. What column type is the when column?
13

  1. I can check my MySQL query on PhpMyadmin but it doesn’t understand $_timestamp

  2. My Column when :

image

But in my case i want use the time when the query is exectued to draw my graph, you see ?

14

  1. $_timestamp - where does that come from? Make sure that you run the generated query and not the query with the macros (a macro is a helper function that we have written in Grafana to make it easier to write sql queries). There is a button under the query field called “Generated Sql”. Use that query.

  2. Do you know how to check the column type? Is it datetime or timestamp or something else?

  3. For the “as metric” column, don’t use “when”, try another column that is a string/varchar.

15

Dear All,
I’m using Grafana with docker on a synology. My DB is PhpMyAdmin which I connected to Grafana with mysql plugin.
As far, it seems that it works.
regarding the spec of mysql help section, I didn’t manage to fulfill it:

SELECT
min(UNIX_TIMESTAMP(time_date_time)) as time_sec,
max(value_double) as value,
metric1 as metric
FROM test_data
WHERE $__timeFilter(time_date_time)
GROUP BY metric1, UNIX_TIMESTAMP(time_date_time) DIV 300
ORDER BY time_sec asc

Capture_d'écran_081517_084551_AM

In this example, can you help to complete the request to plot “T1_PAPP” as function of time ?
Maybe the structure of my DB is not correct for Grafana? So how should it be?

Thanks a lot for your precious help!

Cheers,

Joël.

16

Your function of time is column timestp not T1_PAPP right ?

17

Hi Viapass.
Yes, right. The difficulties are to identify what is what in the typical query above
So x axis=timestp or date or time
y axis is T1_PAPP

Thanks!
Joël.

18

Hi there,

I answer to myself. I found a solution on a forum dedicated to my domotic system.
my setup:
-synology
-phpmyadmin as mysql DB
-Grafana on docker on synology.

The sql query as follow, even if I don’t understand all, works:

SELECT
UNIX_TiMESTAMP(e.date_debut)as time_sec,
e.peripherique,
e.valeur as value,
p.libelle as metric
FROM environnement e, peripherique p
where e. peripherique=p.id and e.peripherique = xxxx
order by e.date_debut

Capture_d'écran_081817_092200_AM

Capture_d'écran_081817_092359_AM

Cheers,

Joël.