Reduce output from Aggregation based on date range

[MRussell (Mark Russell)]

Hi

I am new to Elasticsearch and trying to find my way through the development language.

Is it possible to reduce the number of records returned from an aggregation based on the aggregated field?

I have seen a couple of examples where this has been done using the "script" functionality on an integer, but I would like to be able to do it on a dynamic date (now-???).

This is what I have so far and the field I want to reduce on is "max_login".

Does anyone have any suggestions?

Thanks
Mark

{
"size": 0,
"query": {
"bool": {
"must": [
{
"query_string": {
"query": "action: authenticated"
}
},
{
"range": {"@timestamp": {"gte": "now-90d/d"}}}
]
}
},
"aggs": {
"by_username": {
"terms": {"field": "username.keyword",
"size": 10},
"aggs": {
"max_login": {"max": {"field": "@timestamp"}}
}
}
}
}

[MRussell (Mark Russell)]

Ok, I have it working using the bucket filter. My next challenge is, how do I format the date in the filter to be a "YYYY-MM-DD" format?

GET /redshift-dev*/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "action: authenticated"
          }
        },
        {
          "range": {"@timestamp": {"gte": "now-90d/d"}}}
      ]
    }
  },

  "aggs": {
    "by_username": {
      "terms": {"field": "username.keyword",
      "size": 10},
          "aggs": {
            "max_login": {"max": {"field": "@timestamp","format": "YYYY-MM-dd"}},
          
          "login_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "maxLogin": "max_login"
                        },
                        "script": "params.maxLogin < **1529525488770L**"
                    }
                }
          }
    }
  }
}

[MRussell (Mark Russell)]

Solved it, finally.

The following code works out if a user has not logged in for the last 90 days, with the scope of logins being since the last scan (6 months ago).

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "action: authenticated"
          }
        },
        {
          "range": {"@timestamp": {"gte": "now-180d/d"}}}
      ]
    }
  },
  
  "aggs": {
    "by_username": {
      "terms": {"field": "username.keyword", "size": 2147483647},
          "aggs": {
            "max_login": {"max": {"field": "@timestamp", "format" : "YYYY-MM-dd"}},
          "login_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "maxLogin": "max_login"
                        },
                        "script": "params.maxLogin <= new Date().getTime()- 7776000000L"
                    }
                }
          }
    }
  }
}