Statusmap plugin for Grafana | Mysql

[OfCores]

I try to use the "statusmap" plugin by flant display data of an activity log.
https://grafana.com/grafana/plugins/flant-statusmap-panel
My datasource: Mysql
Server: raspberry pi
Grafana: latest version
Plugin: latest version

The example for using the plugin in mysql says:
SELECT $__timeGroupAlias(date_insert,$__interval), name AS metric, min(statusi) AS "statusi" FROM coffee_makers WHERE $__timeFilter(date_insert) AND statusi=1 GROUP BY 1,2 ORDER BY $__timeGroup(date_insert,$__interval)

I'm new in displaying data with grafana and I'm not an expert in sql language.
But I really want to get this working :).

1. Has anyone an example of his sql code for the chart?
2. In addition, what does "$__timeGroupAlias" mean?
3. and "date_insert" should be replaced by what?
4. what means "statusi"?

I know those are a bunch of questions, but maybe anyone could help me anyway.
Thanks for answering

Kind regards
William from Germany

[rocketfixer]

I was also trying to make it work with PostgreSQL using a similar query

SELECT
  $__timeGroupAlias(aeh.date,$__interval),
  ae.name AS metric,
  min(value::numeric) AS "status"
FROM asset_equipment_health aeh
JOIN asset_equipment ae ON aeh.equipment_id = ae.id
WHERE
  $__timeFilter(aeh.date) AND aeh.value::numeric = 1
GROUP BY 1,2
ORDER BY $__timeGroup(aeh.date,$__interval)

but no success.
Some help would be appreciated

Kind Regards

[diafour]

Guten Tag! The Mysql datasource has two modes: "Query builder mode" and "Raw SQL mode". The query from the README is a query built by "Query builder" and then copied from "Raw SQL mode".

In "Query builder" it looks like this:

I've got a table "coffee_makers". It is made for test purposes:

mysql> describe coffee_makers;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| name        | text     | NO   |     | NULL    |                |
| date_insert | datetime | YES  |     | NULL    |                |
| statusi     | int(11)  | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

mysql> select date_insert, name, statusi from coffee_makers;
+---------------------+--------------+---------+
| date_insert         | name         | statusi |
+---------------------+--------------+---------+
| 2021-02-18 16:08:13 | moon-station |       0 |
| 2021-02-18 16:08:43 | moon-station |       2 |
| 2021-02-18 16:09:13 | moon-station |       3 |
| 2021-02-18 16:09:43 | moon-station |       1 |
| 2021-02-18 16:10:13 | moon-station |       0 |
| 2021-02-18 16:10:43 | moon-station |    NULL |
| 2021-02-18 16:11:13 | moon-station |       3 |
...
| 2021-02-18 16:08:13 | gerry-bistro |       1 |
| 2021-02-18 16:08:43 | gerry-bistro |       0 |
| 2021-02-18 16:09:13 | gerry-bistro |       0 |
| 2021-02-18 16:09:43 | gerry-bistro |       0 |
| 2021-02-18 16:10:13 | gerry-bistro |       0 |
| 2021-02-18 16:10:43 | gerry-bistro |       0 |
| 2021-02-18 16:11:13 | gerry-bistro |       3 |
...
+---------------------+--------------+---------+

Purpose of these columns:

• "name" — a string for Y axis
• "date_insert" — a datetime for X axis
• "statusi" — an integer with status observed at "date_insert"

Column names are not important, you can name them as you want.

As you can see in the "Query builder", you need a "Time column" (I choose "date_insert"), a
"Metric column" (I choose "name"), and a value column (I choose "statusi").
Next, you should define a where clause to filter rows by the selected time range (i.e. "Last 1 hour", "Last 5 min", etc.). The Mysql datasource helps you with this, it defines a $__timeFilter macros (click "Show help" in "Query builder" to get more information).
Is it enough? No. The selected time range defines a duration called "interval". We can't use time in "date_insert" as is, it should be "normalized". Mysql datasource helps you with this, it defines a $__timeGroupAlias macros to group rows by intervals. Also, we group rows by "name" to get only one value for one timestamp.
Are we there yet? No! There are multiple rows for the pair (interval, name) and we should aggregate a "statusi" column, for example, with "max" function.
At this point the query is complete and the statusmap is able to show you something like this:

Is this a good query? Honestly, no. As you can see, we show only "max" status for each interval, we've lost other statuses. Here is the most tricky part: to "extract" all statuses for a particular interval we need to define multiple queries — one query for one possible status value, similar to a Prometheus setup here: https://github.com/flant/grafana-statusmap/#panel.

For example, coffee_makers has 5 possible statuses with values from 0 to 4. I add an expression statusi=0 to the WHERE clause. Now we see only statuses with value 0:

Next, we add 4 more queries for statusi=1, statusi=2, etc. Look, we've got a bucket with two statuses: 0 and 1!

Just one last thing: "discrete statuses". We consider that each status is represented by a numeric value, but numerics are for computers. Humans should see more descriptive statuses. So, go to Options->Colors and define a description and a color for each value:

Thank you to follow me on this journey to a good looking and human-friendly graph:

p.s. The PostgreSQL datasource should have similar macros.
p.p.s. This definitely should be part of the documentation.

[OfCores]

Thanks a lot
for your detailed answer to my issue😁

I'll try it as you've written.

Kind regards

[nocandoitagain]

I'm having an issue with statusmap v0.4.2 and grafana v8.4.3. I also tested statusmap v0.4.1 with the same results.

This is the database I'm getting the data from:

describe sb6190_v1;
+-------------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(20) | YES | | NULL | |
| date_insert | datetime(6) /* mariadb-5.3 */ | YES | | NULL | |
| statusi | int(1) | YES | | NULL | |
+-------------+-------------------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

When I follow the above instructions I end up with grafana sql statements like these:

SELECT
$__timeGroupAlias(date_insert,$__interval),
name AS metric,
max(statusi) AS "statusi"
FROM sb6190_v1
WHERE
$__timeFilter(date_insert) AND
statusi = 0
GROUP BY 1,2
ORDER BY $__timeGroup(date_insert,$__interval)

I also configured discrete mode and bucket values/colors in the panel options.

I have 3 values for statusi (0, 1, 2) and 32 values for name. I added queries for the 1 and 2 stausi values, but I only get a blank panel.

However, if in the where clause I select one of the values for name instead of statusi I do get a plot. To get the plot I'm interested in, I have to create 32 "name" queries, instead of the 3 "statusi" queries.

How can I correct this? Thanks in advance for your help.

[OfCores]

I tried it out, but it still doesn't work.
But I think that isn't a syntax issue but an issue with grafana.

I tried to display the data via a chart and firsteval it didn't work until I averaged the data then it bizzarly worked.
So the data base should contain the right data. But why doesn't it work with the statusmap?

Any suggestions?

[diafour]

The blank panel might be a combination of Grafana 7.4.x and statusmap v0.3.4. If this is your case, either downgrade Grafana and use Grafana 7.3.x+statusmap v0.3.4 or upgrade plugin and use Grafana 7.4.x and statusmap v0.4.1.

[OfCores]

I checked the official grafana website, but they just provide the older version of the plugin.
But if I try to install the plugin manually (I'm not 100% sure if I did it the right way(I just downloaded the plugin via github and copied it into the grafana plugins folder)) the grafana log has errors:
lvl=warn msg="Plugin missing module.js" logger=plugins name=Statusmap warning="Missing module.js, If you loaded this plugin from git, make sure to compile it." path=/var/lib/grafana/plugins/grafana-statusmap-master/src/module.js

[diafour]

Master branch contains only sources. You need a released zipfile. For example https://github.com/flant/grafana-statusmap/releases/download/v0.4.1/flant-statusmap-panel-0.4.1.zip

[OfCores]

Thanks for the support diafour,
It finally works.
Really appreciated your patience
😊

[diafour]

Congrats! Good luck with the further experiments with Grafana!