How to work around ES|QL lack of support for unique results

Elastic Stack Kibana
1

Hi everyone,

I am doing my first steps with analysing reports on Kibana, and after several hours I keep failing to find a way to produce unique results with ES|QL, or KQL as alternative.

It seems to me that it is rather strange that such a common case requires exporting the data into CSV, for handling with external tooling.

If this is documented, it doesn't seem to be easy to spot.

Regards, and thanks in advance,
Paulo Pinto

2

Hi @pjmlp Welcome to the community.

Apologies not exactly sure what you are trying to accomplish.

The best suggestion I have is to provide a simple example of what you are trying to do.

A couple sample documents and sample results.

Then perhaps we can help

3

Hi @stephenb thanks for jumping in,

basically I want to duplicate the same functionality like SQL DISTINCT kind of approach,

So lets say I have,

from logs-*-*
| where ip.dst is not null and @environment == "Prod" and @type == "traffic.vpc"
| keep ip.dst, port.dst
| sort ip.dst, port.dst
| limit 10000

How do I remove the duplicate entries from the result set without having to export into a CSV file to perform that?

4

Not sure if there are a better way because the examples in the documentation are pretty basic, but what I normally do is to use a stats to get the last timestamp

In your case would be something like this, the stats line would get the last event for each combination of ip.dst and port.dst, which would result in unique entries.

from logs-*-*
| where ip.dst is not null and @environment == "Prod" and @type == "traffic.vpc"
| stats timestamp = MAX(@timestamp) by ip.dst, port.dst
| keep ip.dst, port.dst
| sort ip.dst, port.dst
| limit 10000
1 Like
5

Thanks, it seems to do the trick, a bit hacky but better than the whole export to CSV step.

I guess it will have to do.