How to bucket aggregate on document without any numeric field

Elastic Stack Kibana
1

I have an index that has documents representing calls. Every document is a call. I want to report average calls per day from every customer. The information needs to be bucketed by week. How do I do that? There is no numeric field as call number. Count of documents is the call

2

Use terms aggregation and date histogram aggregation.
Doc counts for each buckets will be returned.

3

I want the max and min also per bucket.

4

For example: Every bucket has 7 days worth of documents. I want to show Max and MIN docs per day in a week. THus, if there are 10,12,11,15,16,20,22 docs on Monday-Sunday, 10 will be MIN, 22 will be MAX, and Avg will be (10+12+11+15+16+20+22)/7

5 
GET /kibana_sample_data_logs/_search
{
  "size":0,
  "aggs": {
    "week": {
      "date_histogram": {
        "field": "timestamp", 
        "interval": "week"
      },
      "aggs":{
        "day":{
          "date_histogram": {
            "field": "timestamp",
            "interval": "day",
            "min_doc_count": 0
          }
        },
        "min":{
          "min_bucket": {
            "buckets_path": "day._count"
          }
        },
        "max":{
          "max_bucket": {
            "buckets_path": "day._count"
          }
        },
        "avg":{
          "avg_bucket": {
            "buckets_path": "day._count"
          }
        }
      }
    }
  }
}
6

How do I feed this to a visual?

7

Maybe you need Custom visualisation using vega or vega-lite.

8

Couldn’t I create a new index using your script?

9

Why you want to create a new index?

10

Thanks for responding. New to ELK. Here is my situation: I have documents representing calls made. Each document is a call. I need to group them by weekly by customers , and show average (per week), max etc. using the visualization (data table) didn’t help as I could group index by week and customer. But the metric it showed is total count (over the week). So I created a bucket aggregation using GET method. It shows the aggregated data. My question is how do I use this data in visualization? Do I need to create an aggregated index using GET method OR there is a way to use this GET method script directly in visualization? Something like Sql pass through.

11

To create an aggregated index, transform is the function.

As there is no out-of-the-box visualization to support two staged aggregation (daily -> weekly), maybe creating pivot transform groupby daily date histogram and then visualise that index as you like is the shortest way.

12

What is use of GET method? Just to view data? Can’t be used to create visuals off?

13

Dev Tools is a tool for developing Elasticsearch query DSL.
Most visualisations create there own DSL query and can not use your customized query. The only way is use Custom visualisation using vega or vega-lite.

14

Please see all the links I made earlier.

15

What is the purpose of Dev tools then? More like query tool to view data, but not usable to feed the results to a dashboard?

16

In fact, any requests for Elasticsearch cluster could be executed via Dev tools: cluster API, transform API, reindex API...etc. It is a combinient tool to manage Elasticsearch (not kibana). And of course it could be used to develop query for custom visualisation.

17

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.