# Having clause equivalent

**URL:** <https://discuss.elastic.co/t/having-clause-equivalent/32295>\
**Category:** Elasticsearch\
**Created:** [October 15, 2015, 3:58pm UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295 "2015-10-15T15:58:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gilt](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@gilt](https://discuss.elastic.co/u/gilt)\
**Post date:** [October 15, 2015, 3:58pm UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295/1 "2015-10-15T15:58:18Z")

</div>

hi everyone,  
i am new to elasticsearch.  
i am trying to do something like a keep alive track from a service, it sends every X minutes a new record to ES.  
i want to query all the servers that did not send this message in the last Y minutes.  
(this sounds to me like a group by + having clause equivalent but maybe it can be achieved otherwise)

i would appreciate some help.  
thanks.

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [October 15, 2015, 5:12pm UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295/2 "2015-10-15T17:12:03Z")

</div>

Assuming that your documents have some structure like this:

```auto
{
    "serviceId" : "someServiceA",
    "lastPing" : "2015-11-11T18:19:28"
}

```

and you can make sure that each service has send at least one keep alive doc in the past, you could do a [Terms Aggragation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) for grouping all the docs of one service and then get the doc with the last ping timestamp with a [Max Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-max-aggregation.html) like so:

```auto
GET /test/keepAlive/_search?search_type=count
{
  "aggs": {
    "services": {
      "terms": {
        "field": "serviceId"
      },
      "aggs": {
        "last": {
          "max": {
            "field": "lastPing"
          }
        }
      }
    }
  }
}

```

The result should contain buckets for each service looking something like this, which you can then go through to find out which services havend responded in the last X minutes or so:

```auto
            {
               "key": "someServiceA",
               "doc_count": 3236,
               "last": {
                  "value": 1447265968000,
                  "value_as_string": "2015-11-11T18:19:28.000Z"
               }
            }

```

---

<div class="post-metadata">

**Author:** ![gilt](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@gilt](https://discuss.elastic.co/u/gilt)\
**Post date:** [October 18, 2015, 8:05am UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295/3 "2015-10-18T08:05:02Z")

</div>

hi,  
thanks for the response.  
i have another question.  
can i check if the server sent me two messages in the past x minutes?  
(i guess i could use min on the last ping and max on the last ping and compare them) but can this be achieved in a different way?  
as i understand this is a "having clause" that is not supported in elastic search.  
thanks

---

<div class="post-metadata">

**Author:** ![cbuescher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cbuescher/32/60402_2.png) [@cbuescher](https://discuss.elastic.co/u/cbuescher)\
**Post date:** [October 19, 2015, 1:23pm UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295/4 "2015-10-19T13:23:02Z")

</div>

Hi Gil,  
you could just filter on all docs from the last x minutes with a range filter and then do a terms aggregation like the one mentioned above. That would give you the number of docs per service in that time range. You'd still have to go through the result set and filter out the ones that are below a certain threshhold though. This kind of filtering on aggregation results seems something the new [Bucket Selector Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/2.0/search-aggregations-pipeline-bucket-selector-aggregation.html) (the new pipeline aggregations coming in 2.0) might be good for, but I haven't tried that one myself yet.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 11:44pm UTC](https://discuss.elastic.co/t/having-clause-equivalent/32295/5 "2017-07-05T23:44:03Z")

</div>