# How to aggregate based on latest value?

**URL:** <https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737>\
**Category:** Elasticsearch\
**Created:** [July 19, 2018, 1:17pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737 "2018-07-19T13:17:51Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 19, 2018, 1:17pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/1 "2018-07-19T13:17:51Z")

</div>

Hi,

I am trying to understand if I can do the following aggregation.  
Let's say I have the following documents:

```auto
{
	"_id": "auy6a25C7ofiYFJ1f",
	"my_id": 1,
	"type": "A"
	"timestamp": "2018-10-27 10:00" 
},
{
	"_id": "leYdh6zELBawR1iaK7",
	"my_id": 1,
	"type": "B"
	"timestamp": "2018-10-28 10:00" 
},
{
	"_id": "I96fxaKgVwDElW2vTx",
	"my_id": 1,
	"type": "C"
	"timestamp": "2018-10-29 10:00" 
},
{
	"_id": "niaM0ncHot0JDJL4BH",
	"my_id": 2,
	"type": "A"
	"timestamp": "2018-10-27 10:00" 
},
{
	"_id": "K976Y3B9yTWs4jPavm",
	"my_id": 2,
	"type": "B"
	"timestamp": "2018-10-28 10:00" 
}

```

I want to create an aggregation on type but with context of `my_id` field.  
So if I have 2 ids of elements, I want the aggregation to consider that when `my_id`is 1 then it's type is "C" (latest) and when `my_id` is 2 then it's type is "B" (latest).  
This is based on the latest timestamp of my element.

In the end, I want to be able to get the following result:

```auto
"A": 0
"B": 2
"C": 3

```

Is this possible?  
Thanks!!!!!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 22, 2018, 9:56pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/2 "2018-07-22T21:56:32Z")

</div>

How many unique ids have you got?

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 7:57am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/3 "2018-07-23T07:57:04Z")

</div>

@Mark_Harwood On average should be 500K But can get to 1 - 5 million unique ids.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 8:22am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/4 "2018-07-23T08:22:33Z")

</div>

Tricky. This is the classic "bucket explosion" problem when trying to do behavioural analytics on raw log data. Likely you'll need to create an "entity centric" index to sit alongside your event store.

See [this video](https://youtu.be/yBf7oeJKH2Y?t=2m6s) for background and [example scripts and data](http://bit.ly/entcent6_3).

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 9:34am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/5 "2018-07-23T09:34:38Z")

</div>

@Mark_Harwood This is super cool! and it might work for me.  
There is still something that is missing, I think.  
If I will go with this approach, I will end up with 2 indexes, one for my logs, and one for my entity.

So one index will have:

```auto
{
	"_id": "auy6a25C7ofiYFJ1f",
	"my_id": 1,
	"type": "A"
	"timestamp": "2018-10-27 10:00" 
},
{
	"_id": "leYdh6zELBawR1iaK7",
	"my_id": 1,
	"type": "B"
	"timestamp": "2018-10-28 10:00" 
},
{
	"_id": "I96fxaKgVwDElW2vTx",
	"my_id": 1,
	"type": "C"
	"timestamp": "2018-10-29 10:00" 
},
{
	"_id": "niaM0ncHot0JDJL4BH",
	"my_id": 2,
	"type": "A"
	"timestamp": "2018-10-27 10:00" 
},
{
	"_id": "K976Y3B9yTWs4jPavm",
	"my_id": 2,
	"type": "B"
	"timestamp": "2018-10-28 10:00" 
}

```

And the other will have:

```auto
{
    "my_id" :1,
    "type": "C"
},
{
    "my_id": 2,
    "type", "B"
}

```

Which is cool. But then I how can I do the following:  
filter logs based on timestamp query for example, and then only on entities which have been seen in this window do the aggregation by type. (essentially I need some connection between the 2 entities)

So if I want to filter all logs which timestamp is greater and equal to "2018-10-28 10:00" and then group by entity type.  
The end result should be:

```auto
"B": 2
"C": 2

```

What do you think?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 9:52am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/6 "2018-07-23T09:52:36Z")

</div>

> [@tpraizler](#):
>
> So if I want to filter all logs which timestamp is greater and equal

Not sure exactly what business problem you're trying solve but you don't need to keep just one "type" attribute. You could hold "lastSeen", "firstSeen" dates and "minutesInStateB" durations etc if they are of use. If I understand your problem correctly you'd want to summarise entity types by those entities with a "lastSeen" date greater than or equal to "2018-10-28 10:00" .

```
GET entities/_search
{
  "query": {
	"range": {
	  "lastSeen": {
		"gte":"2018-10-28 10:00"
	  }
	}
  },
  "aggs":{
	"types":{
	  "terms":{
		"field":"type"
	  }
	}
  }
}

```

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 11:09am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/7 "2018-07-23T11:09:26Z")

</div>

Think of it as an IOT device. I have logs and an IOT device entity.  
I want to query for all the logs between date x and date y, and I want to take all relevant devices and group by their type.

In SQL it would be:

```auto
SELECT d.type, count(*) FROM log as l,device as d 
WHERE d.device_id = l.device_id
AND l.timestamp > '2018-07-20 10:00'
AND l.timestamp < '2018-07-21 10:00'
GROUP BY d.type

```

Going with your suggestion I might have a field on the device entity with all the timestamps of logs it was seen. it can get up to a couple of thousands. and then I can do:

```auto
GET entities/_search
{
  "query": {
	"range": {
	  "lastSeen": {
		"gte":"2018-10-28 10:00",
        "lte": "2018-10-29 10:00"
	  }
	}
  },
  "aggs":{
	"types":{
	  "terms":{
		"field":"type"
	  }
	}
  }
}

```

And it might work. but as long as I expend my filter on the logs it becomes harder right?  
I mean if I want to do the following:  
query for all the logs of log\_type "x" and severity which equal 7 from date x and date y, and I want to take all relevant devices and group by their type (device\_type).

In SQL it will be:

```auto
SELECT d.type, count(*) FROM log as l,device as d 
WHERE d.device_id = l.device_id
AND l.log_type = 'X'
AND l.severity = 7
AND l.timestamp > '2018-07-20 10:00'
AND l.timestamp < '2018-07-21 10:00'
GROUP BY d.type

```

How would I do that?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 11:27am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/8 "2018-07-23T11:27:41Z")

</div>

> [@tpraizler](#):
>
> all the logs of type "x" and severity which equal 7 from date x and date y, and I want to take all relevant devices and group by their type.

The key bit of your question is about `type` which changes over time so there's a number of open questions here. When you say find "all the logs of type X" and "group by type" you need to qualify for each of these expressions if you mean the type as at:  
a) the time of the sev 7 log  
b) date Y  
c) now

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 12:17pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/9 "2018-07-23T12:17:15Z")

</div>

So I want all logs of type X and do a group by the device type.  
I assume the most reasonable option would be option b, which is the type of the device on date y.  
The idea is to do the actual group by on the latest term **in that time window** , which is option b.

If this will be too hard to implement, I can go with option c which is now.  
But option b is what I need.

Any idea on how to implement that?

Does it mean I need to keep make every timestamp term into a complex object of timestamp and device\_type?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 12:46pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/10 "2018-07-23T12:46:37Z")

</div>

option b may be hard if the date Y is any random choice of time value.

How many events per device? If they are smallish in number you could use `nested` event logs in a device entity so that your `sev 7 and date x to y` criteria could efficiently test properties of nested event objects but report back a match on the containing device and its lastSeen type

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 1:46pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/11 "2018-07-23T13:46:15Z")

</div>

@Mark_Harwood I might have 400K events per device and more but an average of 10K..Sound big..😑  
Any more tricks we can do? I am dying to make this happened!

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 2:19pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/12 "2018-07-23T14:19:45Z")

</div>

> [@tpraizler](#):
>
> Sound big

yeah.

I expect your app will have to do 2 query passes - one on the event store to get bad device IDs then one or more queries (depending on volumes of ids) to get latest device types of given IDs on or before date Y.

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 23, 2018, 2:24pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/13 "2018-07-23T14:24:49Z")

</div>

I see. Amm this might work.  
Just so I will understand what are my options, but how many ids should be used in one query?  
Can I get a list of 200K ids and query the other index with it?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 23, 2018, 2:29pm UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/14 "2018-07-23T14:29:33Z")

</div>

> [@tpraizler](#):
>
> Can I get a list of 200K ids and query the other index with it?

That's a number you'll have to experiment with I'm afraid. It will depend on how many shards you have. There's limits on how many "buckets" an aggregation can make. For each of your IDs you'll need to get the type of the max date (last sighting) for the query's date range

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 24, 2018, 10:54am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/15 "2018-07-24T10:54:14Z")

</div>

@Mark_Harwood That might (maybe not optimal) for search, but I cannot do group by this way.  
This mean that I will need to do the group by in the application level?

Also if I will need the latest type, and not the one which is the latest in that time window, I will need to do the same right? If I use the latest it won't help in any way?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 24, 2018, 10:59am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/16 "2018-07-24T10:59:43Z")

</div>

> [@tpraizler](#):
>
> This mean that I will need to do the group by in the application level?

Yep. Unless you hit an entity-centric index for the second part of the query where you only store the "lastSeen" type. In that case you'd only return aggregate counts for each type (whereas the event-centric store would more verbosely give you the latest types for each id). Either way, with multiple requests the client app will have to do some of this grouping.

---

<div class="post-metadata">

**Author:** ![tpraizler](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tpraizler/32/46271_2.png) [@tpraizler](https://discuss.elastic.co/u/tpraizler)\
**Post date:** [July 24, 2018, 11:13am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/17 "2018-07-24T11:13:19Z")

</div>

I see. Thanks!

---

<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:** [August 21, 2018, 11:20am UTC](https://discuss.elastic.co/t/how-to-aggregate-based-on-latest-value/140737/18 "2018-08-21T11:20:19Z")

</div>

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