ES|QL date histogram?

Elastic Stack Kibana
,
1

I’m really trying to start to use ES|QL…

About six months ago, I generated some queries (saved search sessions) and was wondering why (unlike the Classic search) there was no date histogram displayed. I was playing around with another ES|QL query just now, and wanted to check out how to structure a WHERE clause and stumbled across this documentation section: Analyze and Visualize Data and - wait, there’s a date histogram!

I composed and ran a simple query and I see a histogram; my saved ES|QL query does not have one. My saved query has a KEEP @timestamp along with other fields (the query is “FROM | WHERE | LIMIT | KEEP”) and it appears the KEEP is what breaks the functionality to show the date histogram. This surprises me because I got the impression that ES|QL was supposed to provide all the logic for a query/report.

I have updated my saved search session to use the ES|QL query without a KEEP and my formatting (i.e., fields to display) specified using the Result pane (choosing the “Toggle Column in Table” tick box for the fields) for the search. Now I have a date histogram but I’m wondering if this is as intended - or whether this is explained somewhere.

I will say that I’d been so used to date histograms for situational awareness of documents from a query that I’d reserved trying ES|QL unless it seemed it might offer some advantage that outweighed this; now I expect I can make more use of ES|QL going forward, so this is good news. Maybe I had just missed it, but if this is new functionality (I’m on 9.2) then I’m very appreciative.

2

Hello @kmp

I found below documentation related to ES|QL date time functions if it can be useful :

Thanks!!

3

Hey! Yes this is intended. When you are KEEPing specific fields, then you don’t have your entire dataset but only these specific fields. This is why we can’t draw a histogram at these cases. The same happens if you use STATS.

So yes this is expected

4

Hi there, and thanks so much for your reply. I guess what I was getting at is that if I KEEP @timestamp, I’d have assumed that those fields and the timestamp would have been sufficient. That doesn’t make sense to me, but hopefully it will become clear in the future.

5

Keep has a different functionality. So if for example you keep one numeric and one string field

image
image3434×1478 333 KB

then you have a nice visualization depicting this information

If you keep the @timestamp it wont display the histogram following the logic I mention above (stats and keep are rendering a different chart if possible). So trying to detect if the user kept a timestamp and only then display the histogram would require some complex calculations.

2 Likes