Aggregations and filters in Elastic - find the last hits and filter them afterwards

Question

I'm trying to work with Elastic (5.6) and to find a way to retrieve the top documents per some category.

I have an index with the following kind of documents :

{
      "@timestamp": "2018-03-22T00:31:00.004+01:00",
      "statusInfo": {
        "status": "OFFLINE",
        "timestamp": 1521675034892
      },
      "name": "myServiceName",
      "id": "xxxx",
      "type": "Http",
      "key": "key1",
      "httpStatusCode": 200
    }
  }

What i'm trying to do with these, is retrieve the last document (@timestamp-based) per name (my categories), see if its statusInfo.status is OFFLINE or UP and fetch these results into the hits part of a response so I can put it in a Kibana count dashboard or somewhere else (a REST based tool I do not control and can't modify by myself). Basically, I want to know how many of my services (name) are OFFLINE (statusInfo.status) in their last update (@timestamp) for monitoring purposes. I'm stuck at the "Get how many of my services" part.

My query so far:

GET actuator/_search
{
  "size": 0,
  "aggs": {
    "name_agg": {
      "terms": {
        "field": "name.raw",
        "size": 1000
      },
      "aggs": {
        "last_document": {
          "top_hits": {
            "_source": ["@timestamp", "name", "statusInfo.status"], 
            "size": 1,
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ]
          }
        }
      }
    }
  },
  "post_filter": {
    "bool": {
      "must_not": {
        "term": {
          "statusInfo.status.raw": "UP"
        }
      }
    }
  }
}

This provides the following response:

{
  "all_the_meta":{...},
  "hits": {
    "total": 1234,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "name_agg": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "myCategory1",
          "doc_count": 225,
          "last_document": {
            "hits": {
              "total": 225,
              "max_score": null,
              "hits": [
                {
                  "_index": "myIndex",
                  "_type": "Http",
                  "_id": "dummy id",
                  "_score": null,
                  "_source": {
                    "@timestamp": "2018-04-06T00:06:00.005+02:00",
                    "statusInfo": {
                      "status": "UP"
                    },
                    "name": "myCategory1"
                  },
                  "sort": [
                    1522965960005
                  ]
                }
              ]
            }
          }
        },
        {other_buckets...}
      ]
    }
  }
}

Removing the size make the result contain ALL of the documents, which is not what I need, I only need each bucket content (every one contains one bucket). Removing the post filter does not appear to do much.

I think this would be feasible in ORACLE SQL with a PARTITION BY OVER clause, followed by a condition.

Does somebody know how this could be achieved ?

What version of Elasticsearch is this? – sramalingam24 Apr 06 '18 at 01:06 — sramalingam24, Apr 06 '18 at 01:06
5.6, my bad. Edited my post. – Matthieu Borgraeve Apr 06 '18 at 14:28 — Matthieu Borgraeve, Apr 06 '18 at 14:28

score 0 · Answer 1 · answered Apr 06 '18 at 17:52

If I understand you correctly, you are looking for the latest doc that have status of OFFLINE in each group (grouped by name)?. In that case you can try the query below and the number of items in the bucket should give you the "how many are down" (for up you would change the term in the filter)

NOTE: this is done in latest version, so it uses keyword field instead of raw

POST /index/_search
{
    "size": 0,
  "query":{
    "bool":{
        "filter":{
            "term": {"statusInfo.status.keyword": "OFFLINE"}
        }
    }
  },
  "aggs":{
    "services_agg":{
        "terms":{
            "field": "name.keyword"
        },
        "aggs":{
            "latest_doc":{
                "top_hits": {
                    "sort": [
                        {
                            "@timestamp":{
                                "order": "desc"
                            }
                        }
                        ],
                        "size": 1,
                    "_source": ["@timestamp", "name", "statusInfo.status"]
                }
            }
        }
    }
  }
}

Hi, not exactly, this will get the last `OFFLINE` status, right ? It would filter out the `UP` status I might have received afterwards ? I need the last statuses (as a first filter) `UP` or `OFFLINE`, then I need to filter out the non `OFFLINE` ones in order to get the currently `OFFLINE` services, "currently" meaning "In the last status they sent". — Matthieu Borgraeve, Apr 06 '18 at 18:14
Yeah that is correct, so you want to aggreagate first then filter, i see it now — sramalingam24, Apr 06 '18 at 19:26

Aggregations and filters in Elastic - find the last hits and filter them afterwards

1 Answers1