# Using a threshold on doc\_count within a nested aggregation

**URL:** <https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357>\
**Category:** Kibana\
**Tags:** elastic-stack-alerting\
**Created:** [January 6, 2021, 3:08pm UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357 "2021-01-06T15:08:07Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pschwippert](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pschwippert/32/81841_2.png) [@pschwippert](https://discuss.elastic.co/u/pschwippert)\
**Post date:** [January 6, 2021, 3:08pm UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/1 "2021-01-06T15:08:08Z")

</div>

Hi,  
I am currnetly working on a watcher that send out an alert when a user has too many failed login attempts. I am aggregating the documents first based on the realm then based on the username.

Now within the compare clause within my watcher script I want to get the doc\_count of the usernames and compare that to the threshold set (in this case an arbitrary number of 20).

Is there any way I can do this? I was researching if array\_compare was a viable solution but according to another thread that does not support nested aggregations.

Thank you for assisting me 🙂

This is my current watcher:

```auto
  {
  "trigger": {
    "schedule": {
      "interval": "10s"
    }
  },
  "input": {
    "search": {
      "request": {
        "search_type": "query_then_fetch",
        "indices": [
          "XXXXXXXXX"
        ],
        "rest_total_hits_as_int": true,
        "body": {
          "query": {
            "bool": {
              "must": [
                {
                  "query_string": {
                    "query": "XXXXXXXXX:LOGIN_ERROR"
                  }
                },
                {
                  "range": {
                    "@timestamp": {
                      "gte": "now-10m",
                      "lte": "now"
                    }
                  }
                }
              ]
            }
          },
          "aggs": {
            "group_by_realm": {
              "terms": {
                "field": "XXXXXXXX.realmId",
                "size": 5
              },
              "aggs": {
                "group_by_username": {
                  "terms": {
                    "field": "XXXXXXXX.username",
                    "size": 5
                  },
                  "aggs": {
                    "get_latest": {
                      "terms": {
                        "field": "@timestamp",
                        "size": 1,
                        "order": {
                          "_key": "desc"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "condition": {
    "compare": {
      "ctx.payload.hits.total": {
        "gte": 50
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [January 6, 2021, 4:00pm UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/2 "2021-01-06T16:00:11Z")

</div>

Yes, this is possible. Your 2-level aggregation requires a scripted comparison. You might be able to simplify the results from ES by using a bucket script pipeline agg, which could do some pre-processing so that your condition is simpler to write.

---

<div class="post-metadata">

**Author:** ![pschwippert](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pschwippert/32/81841_2.png) [@pschwippert](https://discuss.elastic.co/u/pschwippert)\
**Post date:** [January 6, 2021, 4:24pm UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/3 "2021-01-06T16:24:00Z")

</div>

How would I approach this scripted comparison?  
I want to loop over all the values in the realm bucket and then loop over each of the values in those respective buckets to get the doc count.

example structure

- realm Foo
  - user AAA
    - doc 1
    - doc 2
    - doc 3

  - user BBB
    - doc 4
    - doc 5
    - doc 6

I'd set the threshold to 2 for this example. I expect to be able to compare the doc\_count to 2 and then trigger an alert since there are 3 documents for user AAA and BBB

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [January 6, 2021, 5:23pm UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/4 "2021-01-06T17:23:24Z")

</div>

You would write a [script condition](https://www.elastic.co/guide/en/elasticsearch/reference/current/condition-script.html) using Painless. The syntax is similar to Java, so you can use most of the Java array operators to iterate.

---

<div class="post-metadata">

**Author:** ![pschwippert](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pschwippert/32/81841_2.png) [@pschwippert](https://discuss.elastic.co/u/pschwippert)\
**Post date:** [January 7, 2021, 9:06am UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/5 "2021-01-07T09:06:10Z")

</div>

I've researched some more about painless. I think I've got something now however it's complaining about invalid JSON, and I am unable to find what's wrong. Maybe I am just not seeing it.

```auto
"condition": {
    "script": {
      "lang": "painless",
      "inline": "for(realmBuckets in ctx.payload.aggregations.group_by_realm.buckets) {
            for(userBuckets in realmBuckets.buckets) {
                if(userBuckets.doc_count > threshold){
                    return true;
                }
            }
        }
        return false;
        ",
        "params": {
            "threshold": "5"
        }
    }
  },

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 4, 2021, 9:06am UTC](https://discuss.elastic.co/t/using-a-threshold-on-doc-count-within-a-nested-aggregation/260357/6 "2021-02-04T09:06:11Z")

</div>

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