Using a threshold on doc_count within a nested aggregation

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 :slight_smile:

This is my current watcher:

  {
  "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
      }
    }
  }
}

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.

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

You would write a script condition using Painless. The syntax is similar to Java, so you can use most of the Java array operators to iterate.

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.

"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"
        }
    }
  },