# Trying to use sum\_bucket agg to summarize the last value per server into a total
**URL:** https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729
**Category:** Kibana
**Created:** [October 25, 2023, 12:55pm UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729 "2023-10-25T12:55:03Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@mekberg](https://discuss.elastic.co/u/mekberg)
#### Post date: [October 25, 2023, 12:55pm UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/1 "2023-10-25T12:55:04Z")
I'm trying to create a search (ultimately a visualization) that will give me the total number of Controller nodes in a cluster. Each node reports metrics every 15 seconds, and each document will contain the value for that specific node, with the active controller carrying "1" as the value, and all others "0".
My goal is to get the _latest_ value for _each node_, and then _sum_ those values. If the result is anything other than 1, that's bad. Obviously, if I get values from more documents than just the last one per each node, the result will be wrong.
This is my current query:
```auto
GET metricbeat-8.6.2/_search
{
"query": {
"bool": {
"filter": [
{
"exists": {
"field": "prometheus.metrics.kafka_controller_active_controller_count"
}
}
],
"must": [
{
"terms": {
"host.name": [
"node1",
"node2",
"node3",
"node4"
]
}
}
]
}
},
"aggs": {
"Per_Broker": {
"terms": {
"field": "host.name",
"size": 4
},
"aggs": {
"Last_Value": {
"terms": {
"field": "@timestamp",
"size": 1,
"order": {
"_key": "desc"
}
},
"aggs": {
"Active_Controller": {
"sum": {
"field": "prometheus.metrics.kafka_controller_active_controller_count"
}
}
}
}
}
},
"Number_of_Active_Controllers": {
"sum_bucket": {
"buckets_path": "Per_Broker['Last_Value'].Active_Controller"
}
}
},
"_source": false,
"fields": [
"host.name",
"prometheus.metrics.kafka_controller_active_controller_count"
],
"size": 0
}
```
The result:
```auto
{
"took": 19,
"timed_out": false,
"_shards": {
"total": 19,
"successful": 19,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 4043,
"relation": "eq"
},
"max_score": null,
"hits": []
},
"aggregations": {
"Per_Broker": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "node1",
"doc_count": 1081,
"Last_Value": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 1080,
"buckets": [
{
"key": 1698237171688,
"key_as_string": "2023-10-25T12:32:51.688Z",
"doc_count": 1,
"Active_Controller": {
"value": 0
}
}
]
}
},
{
"key": "node2",
"doc_count": 1026,
"Last_Value": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 1025,
"buckets": [
{
"key": 1698237168398,
"key_as_string": "2023-10-25T12:32:48.398Z",
"doc_count": 1,
"Active_Controller": {
"value": 1
}
}
]
}
},
{
"key": "node3",
"doc_count": 992,
"Last_Value": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 991,
"buckets": [
{
"key": 1698237165024,
"key_as_string": "2023-10-25T12:32:45.024Z",
"doc_count": 1,
"Active_Controller": {
"value": 0
}
}
]
}
},
{
"key": "node4",
"doc_count": 944,
"Last_Value": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 943,
"buckets": [
{
"key": 1698237161746,
"key_as_string": "2023-10-25T12:32:41.746Z",
"doc_count": 1,
"Active_Controller": {
"value": 0
}
}
]
}
}
]
},
"Number_of_Active_Controllers": {
"value": 0
}
}
}
```
No matter how many times I run it, the `Number_of_Active_Controllers` bucket has the value 0, even though at least one `Active_Controller` bucket has the value 1 every single time. I would expect this to be 1, since `0 + 1 + 0 + 0 = 1`.
Do I have a problem in my bucket path? This was the only way I could get both a valid path at all, and a metric that was a number, not an object.
**Bonus question** : how would I translate this into a Kibana Metric or Gauge visualization? I've tried every which way, but I can't find any way to get specifically the _last values per node_, the way I can with the above query.
---
### Author:  [@stephenb](https://discuss.elastic.co/u/stephenb)
#### Post date: [October 26, 2023, 5:20am UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/2 "2023-10-26T05:20:56Z")
> [@mekberg](#):
>
> ` "buckets_path": "Per_Broker['Last_Value'].Active_Controller"`
Yeah I don't think your bucket path is correct the Key is not `Last_Value` it would be an actual value ... which in this case is a `@timestamp`
I don't have test data to try this on... but you want the first bucket did you try?
` "buckets_path": "Per_Broker[0].Active_Controller"`
it may even be this
` "buckets_path": "Per_Broker>Last_Value[0].Active_Controller"`
Or this
` "buckets_path": "Per_Broker>Last_Value.0.Active_Controller"`
Because it is `Last_Value` that is a multi bucket and you want the first one, even though you only have one...
---
### Author:  [@mekberg](https://discuss.elastic.co/u/mekberg)
#### Post date: [October 26, 2023, 6:08am UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/3 "2023-10-26T06:08:21Z")
`"buckets_path": "Per_Broker>Last_Value[0].Active_Controller"` logically works, but doesn't give the expected result either.
`"buckets_path": "Per_Broker>Last_Value.0.Active_Controller"` doesn't work at all, it gives the following error: `Validation Failed: 1: No aggregation [Last_Value.0] found for path [Per_Broker>Last_Value.0.Active_Controller];`
`"buckets_path": "Per_Broker[0].Active_Controller"` also somehow logically works, but I can't really see why, because to me it skips `Last_Value` altogether, and either way it's _not_ the first broker I want, I want _all_ brokers and the `Active_Controller` value for _each_ broker, summed together.
The data, at least to reproduce this example, is very simple:
`host.name` is a keyword field, containing the hostname
`@timestamp` is, obviously, the timestamp of the event
`prometheus.metrics.kafka_controller_active_controller_count` (call it whatever you want, really) is just a simple integer value.
Each server, in my case four, produces a value every 15 seconds by way of Metricbeat collecting the data from a JMX plugin. That means every 15 seconds there is a snapshot of the state of the cluster, with (hopefully) just one of the results containing "1", and all the others "0".
Edit: Some other combinations I tried:
`"buckets_path": "Per_Broker[Last_Value.0].Active_Controller"` works, but the result in the sum\_bucket is still 0...
`"buckets_path": "Per_Broker[Last_Value[0]].Active_Controller"` gives error: `Validation Failed: 1: No aggregation found for path [Per_Broker[Last_Value[0]].Active_Controller];`
`"buckets_path": "Per_Broker>Last_Value[0]>Active_Controller"` works, but also still 0
Edit 2: I guess I realize I don't at all understand the exact structure of a "bucket path", or what makes one bucket different from another in a way that requires the path to be different. Either way, it's weird how so many variations of these paths _work_, as in they give no errors and allow a result to emerge in the sum\_bucket, but I have no idea why because I feel like they shouldn't, nor do I know what values it's fetching, since the result is never the one that is expected.
---
### Author:  [@mekberg](https://discuss.elastic.co/u/mekberg)
#### Post date: [October 26, 2023, 7:37am UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/4 "2023-10-26T07:37:36Z")
From what I've read, `terms` is considered a multi-bucket aggregation. Since I use `terms` twice, that must then mean I have a path consisting of a multi-bucket inside another multi-bucket. The last aggregation is a metric aggregation, not a bucket, that should sum up all the (size: 1) documents in the parent multi-bucket.
So, I guess the path would somehow have to be `multi_bucket>multi_bucket.metric`, but since I want the equivalent of `for each` in _both_ multi-buckets, I don't know how to write that, or if that's even possible?
---
### Author:  [@mekberg](https://discuss.elastic.co/u/mekberg)
#### Post date: [October 26, 2023, 8:06am UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/5 "2023-10-26T08:06:44Z")
OK, I believe I may have solved it. Like I half suspected, and which was suggested in another forum post thread I found, `sum_bucket` cannot go so many levels deep, but that person solved it by doing _intermediate_ sum buckets, and then summing _them_ in the end. Brilliant? I reserve my judgment, but at least it does seem to genuinely work.
```auto
"aggs": {
"Per_Broker": {
"terms": {
"field": "host.name",
"size": 4
},
"aggs": {
"Last_Value": {
"terms": {
"field": "@timestamp",
"size": 1,
"order": {
"_key": "desc"
}
},
"aggs": {
"Active_Controller": {
"sum": {
"field": "prometheus.metrics.kafka_controller_active_controller_count"
}
}
}
},
"Is_Controller": {
"sum_bucket": {
"buckets_path": "Last_Value.Active_Controller"
}
}
}
},
"Num_Controllers": {
"sum_bucket": {
"buckets_path": "Per_Broker.Is_Controller"
}
}
}
```
I can confirm it works as I need it to by increasing the limit on the @timestamp terms agg. By setting it to 2, I do the intermediate sum on the same field twice, which is the calculated into the final sum\_bucket as well, meaning even if two different servers were controllers simultaneously, rather than the same one at two points in time, the end result would also be 2 and therefor signal a problem.
Now to figure out how the hell I turn this into a Kibana visualization...
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 23, 2023, 8:06am UTC](https://discuss.elastic.co/t/trying-to-use-sum-bucket-agg-to-summarize-the-last-value-per-server-into-a-total/345729/6 "2023-11-23T08:06:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.