CODEWITHSUNDEEP
elasticsearch
James Fu
James Fu

Reputation: 452

Complex query for Elasticsearch to count the number of document according to different time

For example, I have the data like following:

deviceId time        status
001      Nov.1 9:37  ok
001      Nov.1 19:02 fail
001      Nov.2 8:46  ok
001      Nov.3 10:59 fail
001      Nov.3 21:19 ok
002      Nov.1 9:37  ok
003      Nov.1 9:37  fail
003      Nov.3 2:18  ok
003      Nov.3 11:55 fail
003      Nov.3 15:11 ok
004      Nov.1 9:37 fail

I'd like to obtain the result like following (getErrorCountFrom([001, 002, 003])):

time          error(how many fail devices in this time)
Nov.1 10:00   1
Nov.1 22:00   2
Nov.2 10:00   1
Nov.2 22:00   1
Nov.3 10:00   0
Nov.3 22:00   0

Is it possible to query from Elasticsearch to get this result?

the example diagram

Upvotes: 0

Views: 145

Answers (1)

Benjamin Trent
Benjamin Trent

Reputation: 7566

This is strictly not "search" but you want aggregated results (bucketing by time the number of errors).

So, you need to use an aggregation. Specifically, the date_histogram

The following should be close to what you want (depending on your Elasticsearch version, and mappings).

{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "status": "fail"
          }
        }
      ]
    }
  },
  "aggs": {
    "hourly_count": {
      "date_histogram": {
        "field": "time",
        "fixed_interval": "1h"
      }
    }
  }
}

Upvotes: 2

Related Questions