Getting the last value from an ElasticSearch index

Elastic Stack Logstash
1

Hello, I'm trying to get the latest value from my Elasticsearch index but when I execute this, it prints everything in the index on the console. I have also tried doing it using the "DateTime" field but every time I get all the documents from the index printed.

input {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "earthquake-v2"
    query => '{"sort": [{"Gap": {"order": "desc"}}], "size": 1}'
  }
}

output {
  stdout {
    codec => rubydebug
  }
}

This is my output:

{
"Gap" => "59",
"parsedDateTime" => "2015-12-31T22:26:08.000Z",
"Magnitude" => "4.30",
"Distance" => "2",
"Longitude" => "72.6379",
"MagType" => "Mb",
"@version" => "1",
"Latitude" => "40.8745",
"Depth" => "40.30",
"DateTime" => "2016-01-01 03:26:08",
"Source" => "us",
"RMS" => "1.21",
"@timestamp" => 2024-05-20T07:40:44.169218400Z
}
{
"Gap" => "219",
"parsedDateTime" => "2015-12-31T22:15:43.000Z",
"Magnitude" => "1.09",
"Distance" => "17",
"Longitude" => "-119.6086",
"MagType" => "ML",
"NbStations" => "9",
"@version" => "1",
"Latitude" => "41.8857",
"Depth" => "4.99",
"DateTime" => "2016-01-01 03:15:43",
"Source" => "NN",
"RMS" => "0.10",
"@timestamp" => 2024-05-20T07:40:44.169218400Z
}
{
"Gap" => "115",
"parsedDateTime" => "2015-12-31T21:06:45.000Z",
"Magnitude" => "2.60",
"Distance" => "0",
"Longitude" => "-155.0622",
"MagType" => "ML",
"@version" => "1",
"Latitude" => "19.5748",
"Depth" => "14.40",
"DateTime" => "2016-01-01 02:06:45",
"Source" => "hv",
"RMS" => "0.44",
"@timestamp" => 2024-05-20T07:40:44.166006100Z
}

What I'm hoping for is to just get the last or most recent value from the respective field in my Elasticsearch index which I can then use to further check in my local data file and if more records are there then make a new index. TIA!

2

Thank you for reaching out, @Danyal_Danish. Which version of Elastic are you using? Did your attempt to sort by the "DateTime" field look something like this?

GET earthquake-v2/_search
{
  "sort": [
    {
      "DateTime": {
        "order": "desc"
      }
    }
  ],
  "size": 1
}
3

Hi Jess, tysm for your response. This is my ELK version: v 8.13.2.

Yes, when I execute this query on DevTools, it gives me the desired output (shown below)

GET /earthquake-v2/_search
   {
     "size": 1,
     "sort": [
       {
         "Gap": {
           "order": "desc"
         }
       }
     ],
     "query": {
       "match_all": {}
     }
   }

Output:

{
"took": 0,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 15,
"relation": "eq"
},
"max_score": null,
"hits": [
{
"_index": "earthquake-v2",
"_id": "T8fylI8Bi3gZRTclHAcw",
"_score": null,
"_source": {
"RMS": "0.10",
"@timestamp": "2024-05-20T07:40:44.169218400Z",
"parsedDateTime": "2015-12-31T22:15:43.000Z",
"MagType": "ML",
"Longitude": "-119.6086",
"Depth": "4.99",
"Latitude": "41.8857",
"Gap": "219",
"Magnitude": "1.09",
"Source": "NN",
"DateTime": "2016-01-01 03:15:43",
"Distance": "17",
"NbStations": "9"
},
"sort": [
219
]
}
]
}
}

But using the same query in the Logstash config is not getting me anywhere :frowning: . It just prints the whole index.

4

Sorry, I used the Gap field in the example I showed but it's the same result regardless.

5

Thanks for following up, @Danyal_Danish. Yeah, I would think the query would look something like this:

input {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "earthquake-v2"
    query => '{
      "sort": [{"DateTime": {"order": "desc"}}],
      "size": 1
    }'
  }
}

output {
  stdout {
    codec => rubydebug
  }
}

You may want to double check the your DateTime field is correctly indexed and that the mappings are correct.

6

So, I did check and this how my mappings look:

{
  "mappings": {
    "properties": {
      "@timestamp": {
        "type": "date"
      },
      "DateTime": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      },
      "Depth": {
        "type": "float"
      },
      "Distance": {
        "type": "integer"
      },
      "EventID": {
        "type": "integer"
      },
      "Gap": {
        "type": "integer"
      },
      "Latitude": {
        "type": "float"
      },
      "Longitude": {
        "type": "float"
      },
      "MagType": {
        "type": "keyword"
      },
      "Magnitude": {
        "type": "float"
      },
      "NbStations": {
        "type": "integer"
      },
      "RMS": {
        "type": "float"
      },
      "Source": {
        "type": "keyword"
      },
      "parsedDateTime": {
        "type": "date"
      }
    }
  }
}

And there were some documents in there that had no DateTime field like shown below

image
image1527×420 64.8 KB

So, I rewrote the code and used the @timestamp field like this

input {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "earthquake-v2"
    query => '{
      "sort": [{"@timestamp": {"order": "desc"}}],
      "size": 1
    }'
  }
}

output {
  stdout {
    codec => rubydebug
  }
}

Still the same result, it prints the whole index. Is it maybe because of a parameter I'm missing or is this something that's not possible through logstash because judging from some other answers on other platforms it felt like this is something that people haven't come across.

7

Hi @Danyal_Danish,

I think I saw on Slack that this was resloved. Let us know if you need any further assistance here.

Thanks,

Jessica

1 Like
8

Thank you, yes it was.

1 Like