CODEWITHSUNDEEP
elasticsearchgrafanadashboardcumulative-sum
Chor Hatara Hud'u Keturi
Chor Hatara Hud'u Keturi

Reputation: 641

How can I make a cumulative sum graph in grafana, from an elasticsearch data source?

Relevant info:

There is an ES index that is a log of events over time, and the events are categorised (per country). There is a grafana instance which has this ES index as a data source.

In grafana, I would like to make a graph over time of these events, such that the value for any given date on the x-axis would be the total number of events since the beginning of time until that time, reflected on the y-axis.

Basically, a normal, ordinary cumulative-sum graph.

I have read several tutorials, and nothing actually allows this. There is a lot of irrelevant information about showing cumulative sum on the tooltip, which is not useful to me.

Is this possible? If so, how can I do it?

Upvotes: 9

Views: 10931

Answers (2)

Bim
Bim

Reputation: 531

Not exactly an answer to the original question but since the answer is "It's impossible" I thought I'd share my work-around for this missing feature. As this is what I did instead.

Here is how to do it in MariaDB:

SET @cumul := 0;
SELECT
    `seq`
    , @cumul := @cumul + `seq` AS `cumul`
FROM seq_1_to_20;

The data has to be ordered in sequence for it to add up correctly.

to explain it:

  1. set a variable called @cumul
  2. add the value you want to make cumulative and select it as a column

EDIT:

In Grafana it does not let you run 2 queries at the same time and it also makes use of Connection Pooling, meaning it speeds up the system by running queries over different connections, so if you run them as 2 queries the variable is not reset.

After hacking at it, this version works in Grafana for MariaDB (and probably MySQL too)

SELECT
    `seq`
    , @cumul := @cumul + `seq` AS `cumul`
FROM seq_1_to_20, (SELECT @cumul:= 0) b

Upvotes: 0

Chor Hatara Hud'u Keturi
Chor Hatara Hud'u Keturi

Reputation: 641

After contacting grafana directly I received the answer that this is not currently possible. All such computation must be done by the data source, not by grafana.

Upvotes: 18

Related Questions