02. 11. 2022 Mirko Morandini ITOA, NetEye

Creating Compelling Stacked Bar Charts with Grafana

Grafana is an open source data visualization application that is widely used for displaying interactive monitoring and service dashboards. It focuses on a high-performance visualization of time series data, such as network throughput, access time, or CPU performance data. For this, it connects to specific time series databases such as InfluxDB, but also to common relational databases, such as MySQL and MSSQL.

Beyond plain time series however, you often need to also display diagrams with daily or monthly KPIs. In IT Operations you might need the monthly availability or the daily average network speed, while in service management you have e.g. the number of tickets by day or the monthly average of solution times.

Of course, even non time-related diagrams can be interesting. In other applications, or just in Excel with pivot tables and stacked bar charts, you’ll get a quick win for such reports. In Grafana it’s a bit trickier to group data by time intervals and to display multiple result sets, but it’s worth trying. Let’s try with some diagrams based on MySQL data from a servicedesk application!

“Old” Graphs

I’ll start with the time series graph in Grafana 7, called “Graph (old)” in Grafana 8.  To display e.g. created and closed tickets per month, you need to define multiple queries, each one for a result set. By default, the results are displayed in the same position and thus the smaller bars are hidden. Simply select the option for diagram stacking and you get this result, which is not bad. But it’s difficult to compare values:

With various tricks you can get a more dynamic visualization, similar to grouped bar charts or old school “3D” diagrams in Excel. Just translate the second query by some days (or hours if you use a grouping by day), e.g.:

Query A:  SELECT LAST_DAY(create_time) as time, count(*) as value FROM …

Query B:  SELECT LAST_DAY(create_time)+INTERVAL 5 DAY as time, count(*) as value FROM …

Grafana 8 “New” Graphs

However, Grafana 8 contains a completely new time series graphing module which is not at all compatible with the preceding one. Sadly, it’s no longer possible to stack data from different queries. Grafana now stacks the data of the additional rows extracted by a single query!

This can be easy if your data is already formatted this way, for example with a log of processor data you’ll get a classic processor time view, that sums to 100%.

SELECT date, idle-time, kernel-time, user-time FROM processor_data;

If you have to aggregate the data, it can be more cumbersome: you need to define each row with a sub-query or alternatively, as in this example, by using CASE statements:

  DATE(t.create_time) as time_sec, -- not displayed
  CONCAT("Agent", ' ', user_id) as metric,
  SUM(CASE WHEN YEAR(t.create_time)<= 2020 THEN 1 ELSE 0 END) as "<=2020",
  SUM(CASE WHEN YEAR(t.create_time) = 2021 THEN 1 ELSE 0 END) as "2021",
  SUM(CASE WHEN YEAR(t.create_time) = 2022 THEN 1 ELSE 0 END) as "2022",
FROM ticket t JOIN ...
GROUP BY user_id;

As you can see, these stacked diagrams cannot however display a dynamic grouping of the results, as is possible with Excel Pivot tables or with SQL GROUP-BY statements – in this case we would expect a group for every different year in the result set. If you want to display the data of several years or of several states, you need to hard-code a query (Grafana 7) or a result row (Grafana 8) for every year!

Let’s hope that this feature will be implemented in Grafana for relational databases in upcoming versions.

Ways to Group Data by Month in Grafana

KPI data often needs to be grouped by week, month, year, etc. Grafana provides the $__timeGroupAlias macro for this – you just provide the grouping interval, e.g. 1d, 1M, 1Y. But pay attention, this doesn’t quite work as intended! It simply groups by 24h, by 30 days or by 356 days, counting backwards from the actual date. Even looking at the diagram for New Year’s Eve, the graphs wouldn’t be correct, since it doesn’t take into consideration either months with 31 days or leap years.

SELECT $__timeGroupAlias(t.create_time,1M),
  SUM(CASE WHEN ticket_state_id IN (1,4,6) THEN 0 ELSE 1 END) AS "Closed",
  SUM(CASE WHEN ticket_state_id IN (1,4,6) THEN 1 ELSE 0 END) AS "Open"
FROM ticket t
GROUP BY time ORDER BY time;

Thus you need to use the DB’s native date functions. An easy way to group by month is:

SELECT LAST_DAY(t.create_time) as time

which returns the last day of the month for every timestamp, but in Grafana the bars are displayed in the following month. If you like to display the bars starting from the first day of the month you can use:

SELECT DATE(DATE_SUB(create_time,INTERVAL DAYOFMONTH(create_time)- 1 DAY)) as time

These are just some tips and workarounds to display KPIs as bar charts from MySQL-DBs in Grafana. So now that you have some basis to play around, make your boss happy with astonishing Grafana dashboards!

These Solutions are Engineered by Humans

Are you passionate about performance metrics or other modern IT challenges? Do you have the experience to drive solutions like the one above? Our customers often present us with problems that need customized solutions. In fact, we’re currently hiring for roles just like this as well as other roles here at Würth Phoenix.

Mirko Morandini

Mirko Morandini

Mirko Morandini, PhD, is part of the EriZone team since 2015. As a consultant, he guided the implementation of EriZone in various projects in the DACH area and in Italy.


Mirko Morandini

Mirko Morandini, PhD, is part of the EriZone team since 2015. As a consultant, he guided the implementation of EriZone in various projects in the DACH area and in Italy.

Leave a Reply

Your email address will not be published. Required fields are marked *