...

Open source softwares - Grafana

Back to Course

Lesson Description


Lession - #737 Graphing Non Time Series SQL Data in Grafana


Graphing Non Time Series SQL Data in Grafana

Grafana can diagram time series information from many kinds of information sources very well. Yet, once in a while you simply need to diagram, straightforward non time series information. I.e, information without timestamps, fundamental tables that show straightforward measurements.



For this activity, we can rapidly make another information base named exampledb and permit our grafana data set client select authorizations on it.

mysql


> CREATE DATABASE exampledb;

> show databases;

You should see a new database named exampledb. Lets now create a simple table with some data that we can query.

> CREATE TABLE `exampledb`.`simple_table` (
    `id` int(11>
NOT NULL AUTO_INCREMENT, `username` varchar(45>
DEFAULT NULL, `total` decimal(10,0>
DEFAULT NULL, PRIMARY KEY (`id`>
>
ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

Insert Statement

> INSERT INTO `exampledb`.`simple_table`
  (`username`,
  `total`>
VALUES ('Cat',56>
, ('Dog',35>
, ('Lizard',41>
, ('Crocodile',22>
, ('Koala',26>
, ('Cassowary',29>
, ('Peacock',19>
, ('Emu',10>
, ('Kangaroo',13>
;

Check data exists

> SELECT * FROM `exampledb`.`simple_table`;

Now allow our grafana database user select permissions on the table.

> GRANT SELECT ON exampledb.simple_table TO 'grafana'@'###.###.###.###';

> FLUSH PRIVILEGES;

> quit;
 


Explore the Data in Grafana

Open the Explore tab, select MySQL as the information source and show SQL proofreader. Supplant the default sql with this underneath
code>
SELECT
  username AS metric,
  total as value
FROM exampledb.simple_table
ORDER BY id

Now convert it to a time series by adding NOW(>
as time_sec

SELECT
  NOW(>
as time_sec, username AS metric, total as value FROM exampledb.simple_table ORDER BY id

You can test updating the data in the table, and seeing it in Grafana by opening your mysql prompt and executing,

UPDATE `exampledb`.`simple_table` SET `total` = 50 WHERE `username` = 'Koala';