Aggregation as Filter

[karnamonkster (Karan)]

Hi,
My setup : Elasticsearch & Kibana - v6.4.3
I have data with following fields with following format
@timestamp - date
device_no - string
device_type - string
consumption - float

Now i need to find out in a given time interval how many unique device_no have
sum of consumption = "0"(Since there are readings which may be negative)
Is there any way i could use a metric visualization to set a filter within advanced json or on the search bar to enable me to get count of unique devices with "sum(cons) == 0" for that period

Now i need to

[lukeelmers]

Hi @karnamonkster,

It sounds like the question you are trying to answer is: What is the unique count of buckets matching a specific value?

Assuming I understood your question correctly, this would probably be a use case for bucket selector aggregations.

Unfortunately, these are not yet supported in Kibana, although you can follow along with the relevant Github issues here and here to be notified of any updates.

In the meantime, the closest thing I can think of would be to set up a table visualization, and do a terms aggregation on device_no with a filter of consumption == 0. Assuming you set the aggregation size to a reasonable hypothetical limit, it will show you all of the matching buckets. Then if you toggle on the show total option and set it to count, it will at least give you the count you are looking for at the bottom of the table.

That said, this isn't a great alternative: it can be very inefficient if you have a large count of device_no, and it still doesn't capture the single metric you were looking for. But it's the closest thing that comes to mind without having bucket selector aggs available in Kibana today.

Hope this helps a bit -

Thanks,

Luke

[karnamonkster (Karan)]

Thanks @lukeelmers ,
After working around your suggestion may be you can guide me getting this correct.

I need to plot the devices which have (sum of value = 0) for an interval of 1hour split by device type
I have following fields

• dsn - Unique Device no
• cdate- Date Field
• cons- Consumption Value
• op - Device Code

I might be doing the bucket script incorrectly, but here is what i tried.
My json query input & result

POST /index-2019.03.17/_search
{
    "size": 0,
    "aggs" : {
        "cons_per_hour" : {
            "date_histogram" : {
                "field" : "cdate",
                "interval" : "hour"
            },
            "aggs": {
                "total_cons": {
                    "sum": {
                        "field": "cons"
                    }
                },
                "cons_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "totalCons": "total_cons"
                        },
                        "script": "params.totalCons < 0.001"
                    }
                }
            }
        }
    }
}

Result for Aggregation

  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 29289523,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "cons_per_hour": {
      "buckets": [
        {
          "key_as_string": "2019-03-14T16:00:00.000Z",
          "key": 1552579200000,
          "doc_count": 1,
          "total_cons": {
            "value": 0
          }
        },
        {
          "key_as_string": "2019-03-14T22:00:00.000Z",
          "key": 1552600800000,
          "doc_count": 1,
          "total_cons": {
            "value": 0
          }
        }
      ]
    }
  }
}