Limited time period in Canvas

[axfla]

Hi,

I am trying to make line graphs in Canvas based on an Elasticsearch SQL query. When I use the "preview data" button, the data is here. If I use data from the last 14 days, it works. But for some reason, if I try to make a chart based on data older than 14 days ago, I get an empty graph.

My expression is:

    filters

    | essql

    query="SELECT HISTOGRAM(\"@timestamp\", INTERVAL 30 MINUTES) AS h, COUNT(\"events.tokens.eventType\") AS number FROM \"denyall-*\" WHERE \"@timestamp\" >= CAST('2020-11-10' AS DATETIME) AND \"@timestamp\" <= CAST('2020-11-12' AS DATETIME) AND (\"context.tunnelName\" = 'something1' OR \"context.tunnelName\" = 'something2') GROUP BY h ORDER BY h"

    | pointseries x="h" y="mean(number)"

    | plot defaultStyle={seriesStyle lines="1" bars="0" points="1"}

    | render

The result of this query looks like this:

Am I doing something wrong ?

Thank you.

[dosant (Anton Dosov)]

@axfla,
I've just tested and this worked for me:

Simpler example I've used that worked for me:

filters
| essql 
  query="SELECT HISTOGRAM(\"@timestamp\", INTERVAL 30 MINUTES) AS h FROM \"kibana_sample_data_logs\" WHERE \"@timestamp\" >= CAST('2020-11-01' AS DATETIME) AND \"@timestamp\" <= CAST('2020-11-05' AS DATETIME) GROUP BY h ORDER BY h"
| math "unique(h)"
| metric "Days" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render

Not sure what goes wrong in your case.
Could you simplify your query to validate?
What Kibana version are you using?

[axfla]

Hello @dosant ,

Thank you for your help. I just tried your expression and replaced the index by mine, and still faced the same issue:

Still, the data exists, here is the data preview:

If I remove the upper limit from the expression, I get a result, based on the last 14 days:

I use Kibana 7.9.3.

[clintandrewhall]

Good morning! The easiest way to debug this kind of thing is to add render as="debug" after each portion of the expression. So, for example:

filters | render as="debug" will output a JSON representation of the active filters on the page.

filters | essql {...} | render as="debug" will output JSON of the query results.

...etc.

This technique can help you find precisely where the problem might be. Can you give that a try?

[axfla]

Hello @clintandrewhall ,

Thank you for the tip, I wasn't aware of this debugging feature. Unfortunately, it made me realize someone added a time filter on another slide (we are a few people working on the same canvas), and it applied to my requests... So there is actually no problem. Again, thank you both for your help!