How to query latest record for each record type in data table inside canvas

[tangkalo]

Hi, I have been struggling with what can or cannot be done in Kibana. One thing that we need is to query records from many data sources and be able to display the latest record for each data source.

I googled around and found this similar use case in stackoverflow but unfortunately it wasn't answered.

Kibana Canvas essql Data Table query to select most recent records given several conditions

I tried to see if I can use Elasticsearch Query DSL syntax but it doesn't appear I can use it in any elements created off the workpad(please correct me if I am wrong).

Any information or suggestion will be highly appreciated.

Thanks a lot.

[AClerk]

It will help if you provide sample data and the required outcome.
Generally speaking, without knowing too much about your issue.

Select ID from my_index
Group by ID
order by timestamp desc

[tangkalo]

For example, if I have events coming in like the following: -

| datasource | timestamp | status

| site1 | 8:00 AM | ok
| site2 | 8:02 AM | ok
| site3 | 8:04 AM | warn
| site1 | 8:05 AM | error
| site4 | 8:06 AM | error
| site3 | 8:07 AM | ok
| site2 | 8:08 AM | warn
| site5 | 8:09 AM | ok

Then, I want to display the latest status for each site order by site name:-

| datasource | timestamp | status

| site1 | 8:05 AM | error
| site2 | 8:08 AM | warn
| site3 | 8:07 AM | ok
| site4 | 8:06 AM | error
| site5 | 8:09 AM | ok

How would I use Kibana SQL to do that?

As for the suggested sql, I think all fields in select list need to be in the group by clause except the ones with aggregation, so timestamp needs to be a group-by field or one aggregated(e.g. max(timestamp) ).

Thanks for the response.

[AClerk]

@tangkalo
I have created a data table in Kibana and then imported it into Canvas.
I think this is the easiest way to achieve your results.

In canvas

[tangkalo]

Thanks for the suggestion. I ended up working around it by sorting by timestamp first. Once it's in the data table, I sort by the status field.