ElasticSearch - Return only unique values in query

user_11544323 0
Viewed 54

G'day.

I've been struggling with this one for a while, as I'm new to ElasticSearch and I'd really appreciate some guidance or advice.

My goal is to execute a query that retrieves a list of unique IP values, which is filtered by when they last appeared.

What I'm struggling with is the unique part. I've done some experimenting with "aggs", which worked to get unique values, but I coulnd't figure out a way to still get the results sorted by when they last appeared.

I've included my query below, my goal is to limit the results so no duplicates of "src_ip" are returned while still sorting by @timestamp, or at least retrieving the last found values.

Get _search
{
  "query": {
    "match": {
      "type": "CitrixHoneypot|Ciscoasa"
    }
  },
  "_source": ["@timestamp", "src_ip", "type"],
  "sort": [
    {
      "@timestamp": {
        "order": "desc",
        "unmapped_type": "boolean"
      }
    }
  ],
  "size": 500
}

Any help is much appreciated.

elk elasticsearch curl
1 Answers

Your requested query would be something like below:

{
  "query": {
    "match": {
      "type": "CitrixHoneypot|Ciscoasa"
    }
  },
  "aggs": {
    "unique_ip": {
      "terms": {
        "field": "src_ip",
        "size": 500
      },"aggs": {
        "top_hits": {
          "top_hits": {
            "sort": [{"@timestamp":{ "order": "desc" }}],
            "size": 1
          }
        }
      }
    }
  },"size": 0
}

In this query, first you find the unique src_ips then you select the most latest document using top hits and sort over @timestamp.

user_50889990
Related