Aggr count subjects, unique from addresses and ips

Elastic Stack Elasticsearch
1

Hi All,

I am working on a aggregation and I am looking from a count of uniqueue senders and source ip's derived from a email subject.

I am trying the following but it does not seem to give what I need, I hope some one can help me with this.

Aggration:

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "tags:cmgw AND action:P6* AND _exists_:hdr_subject AND cm_score: [0 TO 90] AND NOT dkim:pass* AND NOT dmarc:pass*",
            "analyze_wildcard": true
          }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "now-5m",
              "lte": "now"
            }
          }
        }
      ],
      "must_not": []
    }
  },
  "aggs": {
    "2": {
      "terms": {
        "field": "hdr_subject.keyword",
        "size": 15,
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "3": {
          "terms": {
            "field": "ip",
            "size": 10,
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "4": {
              "value_count": {
                "field": "hdr_from.keyword"
              }
            }
          }
        }
      }
    }
  }
}

Output:

  "aggregations": {
    "2": {
      "doc_count_error_upper_bound": 38,
      "sum_other_doc_count": 5756,
      "buckets": [
        {
          "3": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 2,
            "buckets": [
              {
                "4": {
                  "value": 409
                },
                "key": "46.236.37.165",
                "doc_count": 409
              },
              {
                "4": {
                  "value": 352
                },
                "key": "46.236.37.155",
                "doc_count": 352
              },
              {
                "4": {
                  "value": 198
                },
                "key": "46.236.37.151",
                "doc_count": 198
              },
              {
                "4": {
                  "value": 2
                },
                "key": "77.238.176.205",
                "doc_count": 2
              },
              {
                "4": {
                  "value": 1
                },
                "key": "62.24.135.68",
                "doc_count": 1
              },
              {
                "4": {
                  "value": 1
                },
                "key": "62.128.193.156",
                "doc_count": 1
              },
              {
                "4": {
                  "value": 1
                },
                "key": "64.147.108.55",
                "doc_count": 1
              },
              {
                "4": {
                  "value": 1
                },
                "key": "91.136.10.27",
                "doc_count": 1
              },
              {
                "4": {
                  "value": 1
                },
                "key": "185.201.17.31",
                "doc_count": 1
              },
              {
                "4": {
                  "value": 1
                },
                "key": "209.85.161.174",
                "doc_count": 1
              }
            ]
          },
          "key": "Fantastic new properties...",
          "doc_count": 969
        }

Expected output:

"aggregations": {
    "2": {
      "doc_count_error_upper_bound": 38,
      "sum_other_doc_count": 5756,
      "buckets": [
        {
          "3": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 2,
            "buckets": [
              {
                "uniqueFromAddr": {
                  "value": 1
                },
                "key": "46.236.37.165",
                "doc_count": 409
              },
              {
                "uniqueFromAddr": {
                  "value": 2
                },
                "key": "56.236.37.13",
                "doc_count": 409
              }
            ]
          },
          "key": "Fantastic new properties...",
          "doc_count": 969
        }]
    }
2

Hello pjanzen,

What you need for this at the end level is a cardinality aggregation

See the example I put together to illustrate here:

PUT /foo/bar/1
{
  "subject":"Hello world",
  "ip":"192.168.1.1",
  "from":"alice@example.com"
}

PUT /foo/bar/2
{
  "subject":"Hello world",
  "ip":"192.168.1.2",
  "from":"alice@example.com"
}

PUT /foo/bar/3
{
  "subject":"Hello world",
  "ip":"192.168.1.1",
  "from":"bob@example.com"
}

PUT /foo/bar/4
{
  "subject":"Hello world",
  "ip":"192.168.1.1",
  "from":"alice@example.com"
}


GET /foo/bar/_search
{
  "size": 0,
  "aggs": {
    "subject": {
      "terms": {
        "field": "subject.keyword",
        "order": {
          "_count": "desc"
        }
      },
      "aggs": {
        "ip": {
          "terms": {
            "field": "ip.keyword",
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "from": {
              "cardinality": {
                "field": "from.keyword"
              }
            }
          }
        }
      }
    }
  }
}

Results in:

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 4,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "subject": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Hello world",
          "doc_count": 4,
          "ip": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "192.168.1.1",
                "doc_count": 3,           <-- Even though this is still 3
                "from": {
                  "value": 2              <-- This is now correctly 2
                }
              },
              {
                "key": "192.168.1.2",
                "doc_count": 1,
                "from": {
                  "value": 1
                }
              }
            ]
          }
        }
      ]
    }
  }
}

I hope that helps!

Cheers,
-Robin-

3

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