Canvas - Values retrieved from ESQL aren't the same as the ones displayed in chart

I made a query which produces the following table:
2021-09-20 17_31_51-Elastic

I've cropped out the other column, but it represents the processes I have documents on, being NrByProcess the count of documents for each process. However, when using the values in Canvas to create an horizontal chart, those values don't correspond to the ones in the table above.
2021-09-20 17_45_58-Elastic
2021-09-20 17_46_11-Elastic

These values don't make sense to me and I don't know where the chart gets them. Could someone help me fix this, please?

Is this timeseries data? The timepicker could be filtering the data.

Yes, it is, every document has a @timestamp. This is the query I have.

SELECT TOP 5 COUNT(processName.keyword) AS NrByProcess, processName.keyword FROM "production*" GROUP BY processName.keyword ORDER BY NrByProcess DESC

Although I'm not sure I understand what you mean. I thought the data that shows on the preview table would be the one used to build the chart. Is the any way to fix the filtering if it is the case?

The data is different in the preview from what I've noticed. Change your timeframe around and you will see the data in the preview won't change.

If you open developer tools and go to the network tab and look at the request when you do a preview data request you will see that the request does not pass in the timefilter. It must process that after the fact. I am not a canvas developer so I don't know exactly how it works, I just know the numbers from data preview and what is rendered are different.

Here is an example of a request from one of the dummy data canvas workpads.

{"batch":[{"request":{"query":"SELECT COUNT(DISTINCT OriginAirportID) as total_airports\nFROM kibana_sample_data_flights","count":1000,"timezone":"UTC","filter":[]},"options":{"strategy":"essql"}}]}

I understand what you mean. I have a Time Filter on the Canvas and I have the timeframe to catch all the documents, and it produces the wrong results in the chart that I mentioned before. Following what you said I made a filter in the chart so it isn't influenced by the Time Filter and it showed the same values as in the Data Preview table. However, my goal is to be able to manipulate the time frame to show the data referring to the defined time frame, so I can't filter out the Time Filter element.
I did notice that this is happening to other older elements that were correct last week and this week it shows a decrease in value, even though I didn't change them, for example, I have an element that shows the number of processes and on Friday the value was at 65 and now it's at 32, no changes to the element at all and preview data shows 65 still. If I filter out the Time Filter element it shows the correct value again. So it must be a recent issue related with the Time Filter element that wasn't happening before...
2021-09-20 18_33_59-Elastic
I noticed this as well, is there any way to check which element failed?

I noticed this last week using 7.14.1 and tried to see if there was any issues reported on github but couldn't find any. Maybe a developer will respond or you can submit an issue on github and will get an official response there. Personally I don't know if this is a bug or not but it does appear to be not user friendly. I would like to see the results in preview results that are used to generate the visualization.

Also I do not know of a way to find failed elements instead of just looking for the failed element icon on the screen.

1 Like

Hi, after testing it out a bit, apparently it was an issue with having multiple pages and each having a Time Filter, they were causing conflicts with each other. So I decided to create a filter group for each Time filter and now it's all good and independent. Thank you for your help :slight_smile:


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