Aggregate by sum and avg in visualization

Elastic Stack Kibana
,
1

Hello,

I am having multiple Docker compose projects of which I collect metrics. I want to see the CPU of the Projects over Time.
That means I need to add the amount of cpu ticks of every document with the same docker.container.labels.com_docker_compose_project and same time and then divide the sum per collected time intervall with the amount of timestamps in one x step.
Example: I collect every 30s metrics, I want to see the metrics of the last 30 days so one step on the x axis is 12h. Now I need to sum the cpu ticks of every compose project and then divide each by 1440 (12h / 30 s).
I could configure the dashboard to always show in 12 hour steps, then I could just divide hard by 1440.
But that would make it hard to show custom intervalls, even harder to change the intervalls in which I collect metrics.
And in case metricbeat is not able to collect all 1440 times in 12h the the result would get wrong.

TL;DR: How to aggregate (sum) in Breakdown and then aggregate the average in vertical axis in Visualization (bar graph).

Thanks for reading my question and Thanks for helping in advance :slight_smile: .

1 Like
2

Hey, I am not sure I get your configuration correctly but I understand that you have a visualization with a breakdown and you want to sum. This can be done in Lens with the collapse by setting

image
image866×1188 53.4 KB

Unfortunately you can't divide with a number though, we don't support formula on the breakdown dimension.

1 Like
3

Hello,
I also saw that Field but without that field on sum it doesn't group.

image
image2787×948 98 KB

Without the collapse by setting it looks like that:
image
image2787×948 108 KB

Thanks for your response.

4

Exactly, this setting collapses the buckets created by the terms in one bucket operating the sum. This is what you want, or did I understand it wrongly?

5

No, I want to aggregate the buckets twice, once per project (sum) and once per timeintervall (avg). So that the values are the ticks per 30 seconds per service but with the minimum intervall not having to be the intervall in which i collect my metrics.

For example monitoring has two containers. And another project matrix has also two containers. A third project reverseProxy has only a single container. Every project has the same "docker.container.labels.com_docker_compose_project" field
Example Metrics (ticks (every datapoint has timestamp diff of 30s)):
monitoring 1. container: [1003, 850, 3301, 4531, 6001, 501]
monitoring 2. container: [805, 654, 1050, 1523, 1725, 405]
matrix 1. container: [452, 500, 653, 805, 502, 620, 406]
matrix 2. container: [1005, 1036, 1050, 1017, 1023, 1025]
reverseProxy first and only container: [600, 670, 700, 654, 320, 436]

That means I want the visualization to have following values (would be named "average ticks of project per 30 seconds") if on step on the visualization is 1m:
Monitoring values: 1:00 1656, 2:00 5202.5, 3:00 4316
Matrix values: 1:00 1496.5, 2:00 1611, 3:00 1537
reverseProxy values: 1:00 635, 2:00 677, 3:00 378

Calculations example for first timeintervall:
monitoring value = avg(sum(1003+805) sum(850+654))=1656
matrix value = avg(sum(452+1005) sum(500+1036))=1496,5
reverseProxy value = avg(sum(600) sum(670))=635

Sorry for the long example, I am not very good at explaining.

6

Thanx for the example, it is very helpful. I see what you want to do. I am afraid this is not possible. The closest I can think is something like that

  • Have the average of ticks as metric
  • Add a datehistogram on the horizontal axis
  • Add a terms breakdown and filter only the values you want (monitoring1, 2)
  • On the terms add a collapse by sum setting
  • Create a new chart per container.

image
image1920×901 100 KB

7

Thanks for your Help. :slight_smile:

8

These values should be named "average ticks of project per 1 minute"??

How about this?

  • Set timestamp for Horizontal axis
  • Have Sum of bytes(ticks) with Advanced Normalize by unit: per minute for Vertical axis.

image
image1444×691 79.8 KB

You may add filtering for Projects and add layer for each Project.

image
image1436×611 57.2 KB

3 Likes
9

Aggregate by sum and avg in visualization - #2 by Stratoula_Kalafateli

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.