# How to get most recent reading from two different documents in an index/machine

**URL:** <https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247>\
**Category:** Elasticsearch\
**Created:** [July 30, 2020, 3:59pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247 "2020-07-30T15:59:56Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [July 30, 2020, 3:59pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/1 "2020-07-30T15:59:56Z")

</div>

I have a machine that sends me temperature metrics that I'm pulling from two different files on that machine with filebeat. I'm trying to get some sort of current status of all the metrics of the machine in a query, and having a hard time because I need to get it from two different documents.

For example:

```auto
PUT machine/_doc/1
{
"temp1": 12,
"@timestamp": 1596123915687
}

PUT machine/_doc/2
{
"pressure1": 3,
"@timestamp": 1595123815687 
}

PUT machine/_doc/3
{
"temp1": 10,
"@timestamp": 1596023915687 
}

PUT machine/_doc/4
{
"pressure1": 1,
"@timestamp": 1595023815687 
}

```

The most recent temp1 is from document 1 and the most recent pressure1 is from doc 2.  
I want in a single query to get the most recent pressure and document for a given machine. Top hits aggregation works for a single document but not over different documents. I also debated transforms but they also have a hard time pulling over multiple documents

Any help?

---

<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 30, 2020, 4:09pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/2 "2020-07-30T16:09:28Z")

</div>

I imagine you could use a [filter aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html) with an `exists` query for the relevant fields to segregate the two doc types?

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [July 30, 2020, 4:17pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/3 "2020-07-30T16:17:17Z")

</div>

Hi Mark, thanks for the suggestion.

Are you then saying to do a top hits under each of those filter aggregations?

This is what I came up with but it's just quite bulky because I actually have 3-5 different documents with values I need to pull from, and I'll be running this query every 5 mins or so (across 1000 different machines).

```auto
GET machine/_search
{
  "size": 0,
  "aggs": {
    "temp": {
      "filter": {
        "exists": {
          "field": "temp1"
        }
      },
      "aggs": {
        "LatestValue": {
          "top_hits": {
            "size": 1,
            "_source": [
              "temp1",
              "@timestamp"
            ],
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    },
    "pressure": {
      "filter": {
        "exists": {
          "field": "pressure1"
        }
      },
      "aggs": {
        "LatestValue": {
          "top_hits": {
            "size": 1,
            "_source": [
              "pressure1",
              "@timestamp"
            ],
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [July 30, 2020, 4:20pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/4 "2020-07-30T16:20:52Z")

</div>

I was playing with creating a transform for the different values, but I can't get the filter/exists mechanism to work with it. Any painless suggestions? The below only pulls whichever of the 2 documents most recently came, not both. This is from [https://www.elastic.co/guide/en/elasticsearch/reference/7.8/transform-painless-examples.html#painless-top-hits](https://www.elastic.co/guide/en/elasticsearch/reference/7.8/transform-painless-examples.html#painless-top-hits)

```auto
{
  "group_by": {
    "SystemSerialNumber.keyword": {
      "terms": {
        "field": "SystemSerialNumber.keyword"
      }
    }
  },
  "aggregations": {
    "latest_value": {
      "scripted_metric": {
        "init_script": "state.timestamp_latest = 0L; state.last_value = ''",
        "map_script": "def current_date = doc['@timestamp'].getValue().toInstant().toEpochMilli(); if (current_date > state.timestamp_latest) {state.timestamp_latest = current_date; state.last_value = params['_source']['pressure1'];} ",
        "combine_script": "return state",
        "reduce_script": "def last_value = ''; def timestamp_latest = 0L; for (s in states) {if (s.timestamp_latest > (timestamp_latest)) {timestamp_latest = s.timestamp_latest; last_value = s.last_value;}} return last_value "
      }
    },
    "latest_value2": {
      "scripted_metric": {
        "init_script": "state.timestamp_latest = 0L; state.last_value = ''",
        "map_script": "def current_date2 = doc['@timestamp'].getValue().toInstant().toEpochMilli(); if (current_date2 > state.timestamp_latest2) {state.timestamp_latest2 = current_date2; state.last_value2 = params['_source']['temp1'];} ",
        "combine_script": "return state",
        "reduce_script": "def last_value2 = ''; def timestamp_latest2 = 0L; for (s in states) {if (s.timestamp_latest2 > (timestamp_latest2)) {timestamp_latest2 = s.timestamp_latest2; last_value2 = s.last_value2;}} return last_value2 "
      }
    }
  }
}

```

---

<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 30, 2020, 4:23pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/5 "2020-07-30T16:23:02Z")

</div>

> [@seanziee](#):
>
> Are you then saying to do a top hits under each of those filter aggregations?

Yep - your example looks good. Bulky, admittedly but I'm not sure there's much we can do to improve that.

> [@seanziee](#):
>
> I'll be running this query every 5 mins or so

From a performance point of view, adding a `range` query \>= the last-observed timestamp will help avoid replaying all history.

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [August 3, 2020, 7:57am UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/6 "2020-08-03T07:57:41Z")

</div>

> [@seanziee](#):
>
> The below only pulls whichever of the 2 documents most recently came, not both.

Your scripts does not check if the field exists, I think you need to add an `if` statement:

```auto
      "scripted_metric": {
        "init_script": "state.timestamp_latest = 0L; state.last_value = ''",
        "map_script": "def current_date = doc['@timestamp'].getValue().toInstant().toEpochMilli(); if (params['_source']['pressure1'] != null && current_date > state.timestamp_latest) {state.timestamp_latest = current_date; state.last_value = params['_source']['pressure1'];} ",
        "combine_script": "return state",
        "reduce_script": "def last_value = ''; def timestamp_latest = 0L; for (s in states) {if (s.timestamp_latest > (timestamp_latest)) {timestamp_latest = s.timestamp_latest; last_value = s.last_value;}} return last_value "
      }
    },

```

Alternatively you can put a a filter aggregation in front (requires `>= 7.7`) like you did in post 3.

I second the suggestion with the range filter. With your current config the transform would retrieve all historical values every 5 minutes, which will lead to bad performance. Pragmatic solution: use `now-10m`.

Or you add a `group_by` with a `date_histogram` and e.g. `fixed_interval: 5m`, this way you get the last last state _and_ historical values bucketed by 5 minutes (or any other interval you like). Transform (`>=7.7`) will internally use a range filter in this case, so you do not have to add it to the query yourself.

---

<div class="post-metadata">

**Author:** ![seanziee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanziee/32/45151_2.png) [@seanziee](https://discuss.elastic.co/u/seanziee)\
**Post date:** [August 18, 2020, 5:37pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/7 "2020-08-18T17:37:51Z")

</div>

I forgot to respond. It works great, 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:** [September 15, 2020, 5:37pm UTC](https://discuss.elastic.co/t/how-to-get-most-recent-reading-from-two-different-documents-in-an-index-machine/243247/8 "2020-09-15T17:37:56Z")

</div>

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