Get Sum value of an aggregation than group by a term looks not possible

Elastic Stack Kibana
1

Is there a way to get from a field value ( like "devices_connected"), seperated by a term (like "servers") ... the sum of that?

Example:

There are 3 servers sending connected device information into elasticsearch

//1sec
devices_connected{server="clustermember.node1",devicetype="android"} 1
devices_connected{server="clustermember.node2",devicetype="android"} 0
devices_connected{server="clustermember.node3",devicetype="android"} 0
//2sec later
devices_connected{server="clustermember.node1",devicetype="android"} 2
devices_connected{server="clustermember.node2",devicetype="android"} 0
devices_connected{server="clustermember.node3",devicetype="android"} 0
//3sec later
devices_connected{server="clustermember.node1",devicetype="android"} 5
devices_connected{server="clustermember.node2",devicetype="android"} 0
devices_connected{server="clustermember.node3",devicetype="android"} 1
//4sec later
devices_connected{server="clustermember.node1",devicetype="android"} 5
devices_connected{server="clustermember.node3",devicetype="android"} 2
devices_connected{server="clustermember.node3",devicetype="android"} 20

Have tested this in Kibana with all available options.
Result is a visualization of TSVB:

Aggregation: Max of "devices_connected"
Group by: Terms by "prometheus.labels.server.keyword"
Sum: ... not available?

Results in:

A stacked visualization available showing 3 lines (one per server), but not the sum only.

Expectation:

One line only is visible, showing the sum of all connected deviced on the system.
(similar in case of Gauge usage to show only the sum in the Gauge, not having 3 Gauges, one per server)

//1sec
1
//2sec later
2
//3sec later
6
//4sec later
27

In Kibana there is no way to add a Sum behind the Terms aggregation. Is this right? Should I do something completely different?

2

This is what the "Sum bucket" can be used for (field names are different but I think you get the idea):

Screenshot 2020-10-07 at 15.14.44
Screenshot 2020-10-07 at 15.14.441016×1346 53.4 KB

3

Thanks for your hint 8))!, works fine with two remaining limitations:

  • the Sum Bucket looks not available in TSVB, cant find it 8(
  • there is no filter available (in my case in addition to Sum Bucket / Terms / Max and now filter for devicetype: "android", means to see the overall sum only for android devices and another visualization that filters than only for lets say devicetype: "iOS"")
4

You can do this by creating two separate visualizations. If you add a filter while editing the vis, it will be saved along with it and will also be applied if the visualization is put on a dashboard.
SO basically:

  • New vis
  • Configure sum bucket
  • Add android filter
  • Save and add to dashboard
  • Create new vis
  • Configure sum bucket
  • Add ios filter
  • Save and add to same dashboard to have both side by side
5

Cool. Thanx again. This works excellent for most visualization types:

sumbucket
sumbucket708×635 29.1 KB

But for the TSVB Visualization, there is no Sum Bucket in the dropdown-box available.

tsvb
tsvb1080×857 52 KB

Final Question: Looks this is currently in TSVB visualization not possible?

6

You can do the same in TSVB, but it's working a little different there - you need to use the "Group by" functionality to split up the series by your terms or filters, then use the "Series agg" with "Sum" on the max of your metric field:

Screenshot 2020-10-08 at 09.11.41
Screenshot 2020-10-08 at 09.11.413488×952 145 KB

"Series agg" is summing up all series created by "Group by" per time bucket using the specified function - doing essentially the same as the sum bucket, but in a different order