[ES|QL] Selecting top count for an aggregation doesn't seem to work as expected

[nicpenning]

Elasticsearch version 8.18.1 on Windows Server 2019

I am looking for assistance with an ES|QL query to populate a new field with the result of the Top Hit:
Here is an example:

ROW host = "win10-abc", ip = ["192.168.5.45", "192.168.5.4", "192.168.5.45", "192.168.5.77", "192.168.5.45","192.168.5.45","192.168.18.24"]
| MV_EXPAND ip
| STATS count = COUNT(*) BY ip, host
| SORT count DESC

Which results in:

count	ip	host
4	"192.168.5.45"	"win10-abc"
1	"192.168.5.77"	"win10-abc"
1	"192.168.18.24"	"win10-abc"
1	"192.168.5.4"	"win10-abc"

I would like to show just the top result, but here is the kicker, I want this for other rows as well, such as another host, win-11def and return it's highest IP value so that I can have a query that is below the 10,000 ES|QL limit.
I can do this easily in Lens with a KQL filters and aggregations but I want to use the VALUEs on other field to aggregate field values in another field (such as all host names) which can't be done with lens today.

Trying something like this:

ROW host = "win10-abc", ip = ["192.168.5.45", "192.168.5.4", "192.168.5.45", "192.168.5.77", "192.168.5.45","192.168.5.45","192.168.5.18.24"]
| MV_EXPAND ip
| STATS count = COUNT(*) BY ip, host
| STATS top_ip = TOP(ip, 1, "desc"), top_count = MAX(count) BY host

Yields in this result which isn't what I expected:

"top_ip"	"top_count"	host
"192.168.5.77"	4	"win10-abc"

I would expect to see 4 "192.168.5.45" "win10-abc" as my top result.

In short, I would like to aggregate on some values but only select the row with the highest count.

What I am really looking for is a Top result to return based on an aggregation that ES|QL does. I can do this with KQL and Lens today but I wanted the power of ES|QL behind it.

Top commands work well in Lens with KQL, but I want to do this in ESQL.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[astefan]

@nicpenning can you provide a more detailed query, please? I don't understand the requirement given the description and the sample queries provided. Also, I don't have knowledge about what Lens and KQL are doing with TOP; let's ignore them for now :-) and focus only on the ESQL part. Thanks.

You say

I want this for other rows as well, such as another host, win-11def and return it's highest IP value so that I can have a query that is below the 10,000 ES|QL limit.

and your example query only has ROW host = "win10-abc", ip =..... Assuming host can have multiple values (like the additional wind-11def you mentioned) provide the exact expected results you would like to get.

[nicpenning]

Yeah, that above was an attempt to provide some sample data, which isn't great for the bigger picture. Here is a more solid replication of what I am trying to achieve.

Ingest all 7 of these documents to work through a simply example with 1 host:

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:01:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.45"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:00:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.4"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:04:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.45"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:00:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.77"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:14:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.45"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:24:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.5.45"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:00:00Z",
  "host": {
    "name": "win10-abc",
    "ip": ["192.168.18.24"]
  }
}

Uncomment the third line to show that the top IP is not revealed.

FROM logs-test-esql 
| STATS count = COUNT(*) BY host.ip, host.name
//| STATS top_ip = TOP(host.ip, 1, "desc"), top_count = MAX(count) BY host.name
| LIMIT 10

Results in:

Note: I expect that the 192.168.5.45 that has 4 hits be the "top" hit and return that IP accordingly the query below - which is not the case.

and

respectively. Note that this is probably working by design, but I want to provide the IP address that has the most counts.

To expand on this, we can add additional hosts with multiple IP addresses:

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:02:00Z",
  "host": {
    "name": "win11-xyz",
    "ip": ["192.168.200.15"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:03:00Z",
  "host": {
    "name": "win11-xyz",
    "ip": ["192.168.200.15"]
  }
}

POST logs-test-esql/_doc
{
  "@timestamp": "2025-07-08T12:03:05Z",
  "host": {
    "name": "win11-xyz",
    "ip": ["192.168.225.105"]
  }
}

Which running:

FROM logs-test-esql 
| STATS count = COUNT(*) BY host.ip, host.name
| STATS top_ip = TOP(host.ip, 1, "desc"), top_count = MAX(count) BY host.name
| LIMIT 10

Results in a simliar way where the Top IP is not by the aggregate but what I think might be by value (desc?):

The goal is to return the host.ip that is found the most from the results in a given window across all of the data. This allows us to see the Top IP address by count that a host logs in from.

Hopefully this more complete example better explains the goal of an ES|QL query to aggregate values but return a single value that has the highest aggregate.

[astefan]

@nicpenning I understand the use case now. Unfortunately, inlinestats can't help you nor is top.
What you need is this #108385, meaning a top which gives you the ip sorted by count grouped by host.name.

[nicpenning]

Ahh perfect, yes. That should do it, thank you!

[nicpenning]

Closing since this is not th intended use of this feature. Looking forward to the First/Last capabilities as linked earlier.