Canvas showing error instead of 0

Elastic Stack Kibana
#1

Hello,
I have a query that returns 0 records.
The query is also using a filter of "The last 12 hours".
I have 0 documents in the index, for the past 2 weeks, so the query is querying NULL(?).

SELECT COUNT(DISTINCT uuid.keyword) as count
FROM 
(SELECT uuid.keyword, resolved.keyword, acknowledged.keyword, timestamp
FROM "my-index*" 
WHERE x.keyword = 'x'
ORDER BY timestamp_last_updated DESC)
WHERE resolved.keyword = 'false' AND timestamp > NOW() - INTERVAL 5 MINUTES

When I look at the data preview, I see 0.
When I look at the visualisation on Canvas, I get an error icon.

Canvas:
image

Preview:

image
image704×234 10.6 KB

Error due to missing documents:
image

Is this a bug? feature?
How can I show 0, in my case of 0 documents in the index?

Cheers!

#2

The error indicates that the field timestamp_last_updated might not exist. Can you double check the name? Does it work when you remove ORDER BY timestamp_last_updated DESC?

#3

@Marta_Bondyra
Thanks for the reply.
I know what this means, but that is not the case. This is a false error.
I believe that canvas cannot find any document in the index (according to the 12 hours filter), hence this error. This is the root cause and looks like a bug to me.
If I cancel the 12 hours filter, for example, I can see results.

image
Another error which is not realistic.

image
image565×679 79 KB

How do I workaround this?

Thanks!

#4

I'll try to get someone from the Canvas team to check if it's not a bug then. In the meantime can you let us know what version of the stack you're using?

#5

Hi @AClerk

I think this is a limitation with ElasticsearchSQL and sub-selects https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select

Can you flatten it into a single query like this, or am I missing some logic of what this query is trying to do?

select count(distinct uuid.keyword) as count
from "my-index*"
where x.keyword = 'x'
and resolved.keyword = 'false'
and timestamp > now() - interval 5 minutes