Interesting cardinality aggregation query

I am new to writing ES queries. I have an index that contains log data for thousands of nodes and I need to query the following:

Aggregate request source IP addresses that are matched in at least X number of nodes ("agent.name") AND have at least Y amount of events (that match filter criteria) per node.

Output example:
192.168.1.1 - 5 unique node matches where in each node there were at least 10 events matching this IP address."

The closest I could come up with (see below - details redacted which could have broken formatting if you try and run it) only outputs the IPs that match the number of events AND the number of nodes above the required threshold and not the number of events per unique node.

Any input / solutions / links to similar queries would be extremely helpful and appreciated. :slight_smile:

{
    "aggs": {
        "sip": {
            "terms": {
                "field": "source_ip",
                "size": 1000,
                "order": {
                "unique_node": "desc"
                }
            },
            "aggs": {
                "unique_node": {
                  "cardinality": {
                      "field": "agent.name.keyword",
                      "precision_threshold": 100
                 }
            },
            "unique_bucket_filter": {
                "bucket_selector": {
                    "buckets_path": {
                        "totalCard": "unique_node"
                    },
                    "script": "params.totalCard >= ${threshold_nodes}"
                }
            }
           }
        }
    },
    "size": 0,
    "query": {
        "filter": {
            "bool": {
                "must": [
                    { "term": { "key1":  "value1" } },
                    { "term": { "key2":  "value2" } },
                    { "term": { "key3":  "value3" } },
                    {
                    "range": {
                        "@timestamp": {
                        "gte": "now-${threshold_minutes_ago}m",
                        "lte": "now"
                        }
                    }
                    }
                ]
            }
        }
    }
}

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