Hard Limit on Kibana Elasticsearch SQL?

sql

(Rudy Amid) #1

I use this Elasticsearch SQL Query from Kibana's Canvas and I hit a hard limit of 1000 results returned every time.

SELECT Severity FROM "fwlogs-zayofw*" WHERE Severity LIKE '%medium%' AND ThreatName.keyword IS NOT NULL

I even tried adding "limit ALL" at the end of it, and it will still return 1000 results. Anyone know how to get around this, or is the limit there by design?

regards
Rudy


(John Dorlus) #2

Hi Rudyamid,

The default is set to 1000 but you can override that in your ES SQL function using the count argument.

Something like:
essql query="..." count=10000


(Rudy Amid) #3

Hi John,

How do you specify the count=10000 setting in Kibana's Canvas interface? I can only type in the ES SQL query, but not specify any other setting outside of it.


(John Dorlus) #4

You can check the UI by selecting an element, selecting the Data tab on the sidebar, and checking the existing config. There should be an option like count, size input or limit. If you don't see that, let me know what you see.


(Rudy Amid) #5

Ok, I see where you can edit it in the Expression Editor option on bottom right. However, adding the count=15000 gives me this error now. So it looks 10000 is the hard limit?

(and) [query_phase_execution_exception] Batch size is too large, size must be less than or equal to: [10000] but was [15000]. Scroll batch sizes cost as much memory as result windows so they are controlled by the [index.max_result_window] index level setting. (and) [query_phase_execution_exception] Batch size is too large, size must be less than or equal to: [10000] but was [15000]. Scroll batch sizes cost as much memory as result windows so they are controlled by the [index.max_result_window] index level setting.


(John Dorlus) #6

I think you are correct. Seem seems like 10000 is the hard limit.