Lucene query unique values

[Robin020 (R)]

Hi,

I am working in Kibana Canvas and have a datatable with a Lucene query filter.
Now, I only need a lucene query filter which only shows unique values.
Does somebody know the syntax?

[wylie (Wylie Conlon)]

I don't think you can do this using Lucene, but you can do it using ES SQL which is supported in Canvas. I would recommend using that instead.

[Robin020 (R)]

When I use ES SQL and use specific SQL select distinct I got the message that Distinct is not supported...
How should I do this?

[wylie (Wylie Conlon)]

Right, but you can use GROUP BY.

[Robin020 (R)]

When I use this, it works but with duplicates:

SELECT url.domain, summary.up 
FROM "heartbeat*" 
WHERE url.domain 
LIKE '%<DOMAIN>'

When I add GROUP BY, I got a parse error:

Whoops! Expression failed

Expression failed with the message:

[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem(s) line 1:20: Cannot use non-grouped column [summary.up], expected [url.domain]

This is the SQL code:

SELECT url.domain, summary.up 
FROM "heartbeat*"
WHERE url.domain 
LIKE '%<DOMAIN>'
GROUP BY url.domain

[wylie (Wylie Conlon)]

The error message is explaining that you need to add summary.up to your GROUP BY: GROUP BY url.domain, summary.up

[Robin020 (R)]

Ok, tnx.
With doing that, I got still duplicates but that's because summary.up could have 0 or 1.
Because of that, I try to add a time range. I only like to see the information from now - 11 seconds back. This would remove the duplicates because every 10 seconds heartbeat with do his checks.

I tried this:
AND "@timestamp" BETWEEN NOW() - INTERVAL 10 SECONDS

But it doesn't work how I want it. Have you any suggestions?