CODEWITHSUNDEEP
elasticsearch
user647527
user647527

Reputation: 319

Elastic Search (COUNT*) with group by and where condition

Dear ElasticSearch users,

I am newbie in ElasticSearch.

I am confused for how to convert the following sql command into ElasticSearch DSL query ? Can anyone help to assist me.

  SELECT ip,
         count(*) AS c
    FROM elastic 
   WHERE date BETWEEN '2016-08-20  00:00:00' AND '2016-08-22 13:41:09' 
         AND service='http' 
         AND destination='10.17.102.1'
GROUP BY ip 
ORDER BY c DESC;

Thank You

Upvotes: 20

Views: 32290

Answers (1)

Val
Val

Reputation: 217544

The following query will achieve exactly what you want, i.e. it will select the documents within the desired date range and with the required service and destination and then run a terms aggregation (=group by) on their ip field and order the latter in decreasing count order.

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "date": {
              "gt": "2016-08-22T00:00:00.000Z",
              "lt": "2016-08-22T13:41:09.000Z"
            }
          }
        },
        {
          "term": {
            "service": "http"
          }
        },
        {
          "term": {
            "destination": "10.17.102.1"
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_ip": {
      "terms": {
        "field": "ip"
      }
    }
  }
}

Upvotes: 37

Related Questions