Kibana: filter results by aggregated min and max dates from matched id

Question

I want to pass an event_id to Kibana/Elastic Search and find the min and max dates from the @timestamp field for this event_id. Then I want to set the date range to these dates and show all the results. I assume this is doable.

I can get the min and max with this aggregation:

GET /filebeat-*/_search
{
  "query": {
    "match": {
      "event_id": 1234
    }
  },
  "aggs" : {
     "min_date": {"min": {"field": "@timestamp" }},
     "max_date": {"max": {"field": "@timestamp" }}
  }
}

and I can get the results by searching for the specific date range:

GET /filebeat-*/_search
{
  "query": {
    "bool": {
      "filter": {
          "range": {"@timestamp": {"gte": "2020-09-11T13:35:35.000Z", "lte": "2020-09-24T20:35:07.000Z"}}
      }
    }
  }
}

how can I combine the two so that I can just change the event_id and have an auto date range type feature?

EDIT:

I can do this:

GET /filebeat-*/_search
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "event_id": 1234
        }
      },
      "filter": {
        "range": {
          "@timestamp": {
            "lte": "2020-09-25",
            "gte": "2020-09-24"
          }
        }
      }
    }
  },
  "aggs": {
    "min_date": {
      "min": {
        "field": "@timestamp"
      }
    },
    "max_date": {
      "max": {
        "field": "@timestamp"
      }
    }
  }
}

But what I would like to do is something like:

GET /filebeat-*/_search
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "event_id": 1234
        }
      },
      "filter": {
        "range": {
          "@timestamp": {
            "lte": "max_date",
            "gte": "min_date"
          }
        }
      }
    }
  },
  "aggs": {
    "min_date": {
      "min": {
        "field": "@timestamp"
      }
    },
    "max_date": {
      "max": {
        "field": "@timestamp"
      }
    }
  }
}

But this causes the error: "failed to parse date field [min_date]" Is it possible to use the aggregated min and max values to define the date range?

ESCoder ESCoder · Accepted Answer · 2020-09-30T04:37:25

Since you have not provided any sample index data, so applying range query on date type field

Adding a working example with index mapping, data, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "date": {
        "type": "date" 
      }
    }
  }
}

Index Data:

{
    "date": "2015-02-10",
    "event_id":"1234"
}
{
    "date": "2015-01-01",
    "event_id":"1235"
}
{
    "date": "2015-02-01",
    "event_id":"1234"
}
{
    "date": "2015-02-01",
    "event_id":"1235"
}
{
    "date": "2015-01-20",
    "event_id":"1234"
}

Search Query:

{
  "query": {
    "bool": {
      "must": {
        "match": {
          "event_id": 1234
        }
      },
      "filter": {
        "range": {
          "date": {
            "lte": "2015-02-15",
            "gte": "2015-01-11"
          }
        }
      }
    }
  },
  "aggs": {
    "min_date": {
      "min": {
        "field": "date"
      }
    },
    "max_date": {
      "max": {
        "field": "date"
      }
    }
  }
}

Search Result:

"hits": {
    "total": {
      "value": 3,
      "relation": "eq"
    },
    "max_score": 0.44183272,
    "hits": [
      {
        "_index": "stof_64127765",
        "_type": "_doc",
        "_id": "3",
        "_score": 0.44183272,
        "_source": {
          "date": "2015-02-01",
          "event_id": "1234"
        }
      },
      {
        "_index": "stof_64127765",
        "_type": "_doc",
        "_id": "1",
        "_score": 0.44183272,
        "_source": {
          "date": "2015-02-10",
          "event_id": "1234"
        }
      },
      {
        "_index": "stof_64127765",
        "_type": "_doc",
        "_id": "5",
        "_score": 0.44183272,
        "_source": {
          "date": "2015-01-20",
          "event_id": "1234"
        }
      }
    ]
  },
  "aggregations": {
    "max_date": {
      "value": 1.4235264E12,
      "value_as_string": "2015-02-10T00:00:00.000Z"
    },
    "min_date": {
      "value": 1.421712E12,
      "value_as_string": "2015-01-20T00:00:00.000Z"
    }
  }

Kibana: filter results by aggregated min and max dates from matched id

1 Answers