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?

1 Like
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
6

Hi @corey.robertson
Thanks for the info.

  1. I was not aware of such a limitation. Good to know!

  2. I am not sure why the nested query. I inherited it :sunglasses:

Getting the following error
image

I guess this is the reason for the nested query?!

Cheers!~

7

@corey.robertson

Wrong conclusions.
Edited the message above

8

@AClerk Can you share your full query? I wasn't sure why it was ordering in the original query since it's just doing a count.

9

@corey.robertson
I need to know how many documents have resolved.keyword=false. But only in the last minutes, not from the whole index.
I guess that the where clause covers it

WHERE ... timestamp > NOW() - INTERVAL 5 MINUTES

So maybe you are right. The internal query is redundant in this case.

FULL query:

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

Cheers!

10

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