# Group By (Aggregation) to get only latest fields value

**URL:** <https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353>\
**Category:** Elasticsearch\
**Created:** [January 9, 2020, 6:33am UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353 "2020-01-09T06:33:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![arvindK\_sharma](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@arvindK\_sharma](https://discuss.elastic.co/u/arvindK_sharma)\
**Post date:** [January 9, 2020, 6:33am UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/1 "2020-01-09T06:33:05Z")

</div>

I write a query to read the metricbeat file. This gives me the whatever I want but it repeats the value multiple time.

I want to group by this on latest timestamp so I can get only latest record.

Below is my query

string indexName = "metricbeat-7.4.2-" + DateTime.Now.Year.ToString() + "." + DateTime.Now.Month.ToString("00") + "." + DateTime.Now.Day.ToString("00");  
connectionSettings = new ConnectionSettings(connectionPool).DefaultIndex(indexName);  
elasticClient = new ElasticClient(connectionSettings);

```
        string[] systemFields = new string[]
        {
            "system.memory.actual.used.pct",
            "system.cpu.total.norm.pct"                
        };

        var elasticResponse = elasticClient.Search<object>(s => s
            .DocValueFields(dvf => dvf.Fields(systemFields))
            );

```

DSL query

get /metricbeat\*/\_search?pretty=true  
{  
"query" : {  
"match\_all": {}  
},  
"docvalue\_fields" : [  
"system.memory.actual.used.pct",  
"system.cpu.total.norm.pct",  
"system.load.5",  
"docker.diskio.summary.bytes"  
]  
}

---

<div class="post-metadata">

**Author:** ![arvindK\_sharma](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@arvindK\_sharma](https://discuss.elastic.co/u/arvindK_sharma)\
**Post date:** [January 9, 2020, 3:00pm UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/2 "2020-01-09T15:00:43Z")

</div>

Please help

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [January 9, 2020, 3:27pm UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/3 "2020-01-09T15:27:18Z")

</div>

please note that this is a volunteer driven forum, and thus does not come with any guarantee of questions answered, so you may want to wait a little bit longer than a few hours before bumping your message. if you need support, it is [there](https://www.elastic.co/subscriptions) 🙂

For each metric you could execute a search with a size of 1 document to be returned and sort your search.

Or you could do a single search with for [max](https://www.elastic.co/guide/en/elasticsearch/reference/7.5/search-aggregations-metrics-max-aggregation.html) aggregations for each field.

hope this helps!

---

<div class="post-metadata">

**Author:** ![arvindK\_sharma](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@arvindK\_sharma](https://discuss.elastic.co/u/arvindK_sharma)\
**Post date:** [January 9, 2020, 4:48pm UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/4 "2020-01-09T16:48:29Z")

</div>

Thanks Alexander for your suggestion and solution. I know this foram is volunteer but I was very very impatient this time. Sorry for that.

Any sample for second approch will be more helpful if I am not much bothering you.

---

<div class="post-metadata">

**Author:** ![arvindK\_sharma](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@arvindK\_sharma](https://discuss.elastic.co/u/arvindK_sharma)\
**Post date:** [January 10, 2020, 4:01am UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/5 "2020-01-10T04:01:55Z")

</div>

I tried this but it gives me 6 hour old record. like if I hit @11Am then it shows me record of 5AM

GET /metricbeat-7.4.2-2020.01.10/\_search?pretty=true  
{  
"size": 0,  
"aggs": {  
"memory\_aggs": {  
"terms": {  
"field": "system.memory.actual.used.pct",  
"size": 1  
},  
"aggs": {  
"max\_timestamp": {  
"max": {  
"field": "@timestamp"  
}  
}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 7, 2020, 4:02am UTC](https://discuss.elastic.co/t/group-by-aggregation-to-get-only-latest-fields-value/214353/6 "2020-02-07T04:02:00Z")

</div>

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