Get the @timestamp of the document that has the max of a value

Elastic Stack Elasticsearch
1

Hi I need to get the max of memory usage per host in a time range, I think I can do this with the query below, but I also need the @timestamp of the document when the max memory usage happens.

how can I do this?

GET memory-*/_search
{
  "size": 0,
    "query": {
    "bool": {
      "filter": [
        {
          "exists": {
            "field": "memory_per"
          }
        },
        {
          "match_phrase": {
            "group.keyword": "group1"
          }
        },
        {
          "range": {
            "@timestamp": {
              "time_zone": "-03:00", 
              "gte": "2021-10-24T00:00:00.000Z",
              "lte": "2021-11-24T13:02:43.772Z"            }
          }
        }
      ]
    }
  },
  "aggs": {
    "server": {
      "terms": {
        "field": "host.keyword",
        "size": 100
      },
      "aggs": {
        "max": {
          "max": {
            "field": "memory_per"
          }
        }
      }
    }
  }
}
2

You should move this to the Elasticsearch forum.

1 Like
3

Not sure this is the most efficient way but you can use top_metrics a little backwards

Basically it say pull the top timestamp sorted be the max of the metric

You can just substitute in your host name and metrics etc.

the sort fields is the value of the field you sorted on...

GET metricbeat-*/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "exists": {
            "field": "cloudfoundry.container.cpu.pct"
          }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "now-15m/m"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "server": {
      "terms": {
        "field": "cloudfoundry.app.name",
        "size": 5
      },
      "aggs": {
        "max": {
          "top_metrics": {
            "metrics": {
              "field": "@timestamp"
            },
            "sort": {
              "cloudfoundry.container.cpu.pct": "desc"
            },
            "size" : 1
          }
        }
      }
    }
  }
}

Here is my results

{
  "took" : 46,
  "timed_out" : false,
  "_shards" : {
    "total" : 80,
    "successful" : 80,
    "skipped" : 78,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3426,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "server" : {
      "doc_count_error_upper_bound" : 60,
      "sum_other_doc_count" : 2346,
      "buckets" : [
        {
          "key" : "apps-manager-js-green",
          "doc_count" : 360,
          "max" : {
            "top" : [
              {
                "sort" : [
                  0.006
                ],
                "metrics" : {
                  "@timestamp" : "2021-11-24T18:06:12.735Z"
                }
              }
            ]
          }
        },
        {
          "key" : "healthwatch-ingestor",
          "doc_count" : 240,
          "max" : {
            "top" : [
              {
                "sort" : [
                  0.026000000000000002
                ],
                "metrics" : {
                  "@timestamp" : "2021-11-24T18:05:57.986Z"
                }
              }
            ]
          }
        },
        {
          "key" : "autoscale-new",
          "doc_count" : 180,
          "max" : {
            "top" : [
              {
                "sort" : [
                  0.008
                ],
                "metrics" : {
                  "@timestamp" : "2021-11-24T18:06:12.735Z"
                }
              }
            ]
          }
        },
        {
          "key" : "notifications",
          "doc_count" : 180,
          "max" : {
            "top" : [
              {
                "sort" : [
                  0.011
                ],
                "metrics" : {
                  "@timestamp" : "2021-11-24T18:06:12.735Z"
                }
              }
            ]
          }
        },
        {
          "key" : "app-usage-server",
          "doc_count" : 120,
          "max" : {
            "top" : [
              {
                "sort" : [
                  0.005
                ],
                "metrics" : {
                  "@timestamp" : "2021-11-24T18:04:08.808Z"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
1 Like
4

Another way is top_hits which gives the whole document of the with the max value.

GET metricbeat-*/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "exists": {
            "field": "cloudfoundry.container.cpu.pct"
          }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "now-15m/m"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "server": {
      "terms": {
        "field": "cloudfoundry.app.name",
        "size": 5
      },
      "aggs": {
        "max": {
          "top_hits": {
            "sort": [
              {
                "cloudfoundry.container.cpu.pct": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}

example

{
  "took" : 56,
  "timed_out" : false,
  "_shards" : {
    "total" : 80,
    "successful" : 80,
    "skipped" : 78,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3446,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "server" : {
      "doc_count_error_upper_bound" : 61,
      "sum_other_doc_count" : 2359,
      "buckets" : [
        {
          "key" : "apps-manager-js-green",
          "doc_count" : 362,
          "max" : {
            "hits" : {
              "total" : {
                "value" : 362,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "metricbeat-7.15.1-2021.11.24-000042",
                  "_type" : "_doc",
                  "_id" : "71IpU30BiHFJmY36lKvd",
                  "_score" : null,
                  "_source" : {
                    "@timestamp" : "2021-11-24T18:16:12.782Z",
                    "ecs" : {
                      "version" : "1.11.0"
                    },
                    "cloudfoundry" : {
                      "org" : {
                        "name" : "system",
                        "id" : "2679358b-1bad-46a7-8cc3-f59b75bd71f3"
                      },
                      "space" : {
                        "id" : "27d30a8a-5051-4b45-9e8b-a8ff149530a2",
                        "name" : "system"
                      },
                      "container" : {
                        "cpu.pct" : 0.005659721632855186,
                        "memory.bytes" : 23173529,
                        "memory.quota.bytes" : 134217728,
                        "disk.bytes" : 26017792,
                        "disk.quota.bytes" : 1073741824,
                        "instance_index" : 1
                      },
                      "test" : {
                        "name" : "metricbeat-v1-wo-mp"
                      },
                      "tags" : {
                        "source_id" : "4141c46a-376b-4f30-ba0c-17cc7f461dc0",
                        "product" : "VMware Tanzu Application Service",
                        "process_id" : "4141c46a-376b-4f30-ba0c-17cc7f461dc0",
                        "instance_id" : "1",
                        "process_type" : "web",
                        "system_domain" : "system.pcf-full.bvader.net",
                        "process_instance_id" : "bbc02fb0-2c1f-48a3-51cc-a846"
                      },
                      "envelope" : {
                        "ip" : "192.168.16.39",
                        "job" : "diego_cell",
                        "index" : "1e365b85-9dbd-4db8-945b-e3fcdefbe14b",
                        "origin" : "rep",
                        "deployment" : "cf-200961dab8d1d3a72b8b"
                      },
                      "app" : {
                        "name" : "apps-manager-js-green",
                        "id" : "4141c46a-376b-4f30-ba0c-17cc7f461dc0"
                      },
                      "foundation" : {
                        "name" : "elastic-dev-foundation-v1"
                      },
                      "type" : "container"
                    },
                    "event" : {
                      "dataset" : "cloudfoundry.container",
                      "module" : "cloudfoundry"
                    },
                    "metricset" : {
                      "name" : "container"
                    },
                    "service" : {
                      "type" : "cloudfoundry"
                    },
                    "host" : {
                      "name" : "1ec62043-f8e1-43d9-7c6c-b386"
                    },
                    "agent" : {
                      "type" : "metricbeat",
                      "version" : "7.15.1",
                      "hostname" : "1ec62043-f8e1-43d9-7c6c-b386",
                      "ephemeral_id" : "78410f28-88b3-480c-82b1-31b42da408f4",
                      "id" : "d4d4bbdf-7ea9-4e8e-b41b-a575ee4b2378",
                      "name" : "1ec62043-f8e1-43d9-7c6c-b386"
                    }
                  },
                  "sort" : [
                    0.006
                  ]
                }
              ]
            }
          }
        },
......
1 Like
5

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