Elasticsearch - Count and sum bucket with condition

Viewed 1433

My query is:

es_query = {
        "query": {
            "constant_score": {
                "filter": {
                    "bool": {
                        "must": [
                            {"range": {"@timestamp": {"gte": "now-1d",}}},
                            {"query_string": {"query": "hostname: {} ".format(hostname),"analyze_wildcard": True}}
                        ],
                        "should": [
                            {"match": {"metricset.name": "cpu"}},
                        ]
                    }
                }
            }
        },
        "aggs": {
            "group_by_time_interval": {
                "date_histogram": {
                    "field": "@timestamp",
                    "interval": "1h",
                    "time_zone": "PST8PDT",
                    "min_doc_count": 1
                },
                "aggs": {
                    "cpu_used_avg_pct": {"avg": {"field": "system.cpu.total.pct"}},
                },

            }
        }
    }

I am trying to show average cpu for the last 1 days with interval 1 hour. Output is look like this:

        "group_by_time_interval": {
            "buckets": [
                {
                    "key_as_string": "2018-08-25T19:00:00.000-07:00", 
                    "doc_count": 24, 
                    "key": 1535248800000, 
                    "cpu_used_avg_pct": {
                        "value": 0.0
                    }
                }, 
                {
                    "key_as_string": "2018-08-25T20:00:00.000-07:00", 
                    "doc_count": 24, 
                    "key": 1535252400000, 
                    "cpu_used_avg_pct": {
                        "value": 0.0699
                    }
                }, 
                {
                    "key_as_string": "2018-08-25T21:00:00.000-07:00", 
                    "doc_count": 24, 
                    "key": 1535256000000, 
                    "cpu_used_avg_pct": {
                        "value": 0.071
                    }
                }, 
....

I want to count how many times cpu_used_avg_pct is equal to 0. It can be done by a post script like:

count = 0

for res in results['aggregations']['group_by_time_interval']['buckets']:
    cpu_total_avg = res['cpu_used_avg_pct']['value']
    if float(cpu_total_avg) == 0.0:
        count += 1 

However, is there any way we can avoid additional script to handle it ? Can we add filter directly to ES query.

Thank you so much. Alex

0 Answers
Related