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

tangkalo · February 22, 2021, 10:22pm

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.

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 · February 23, 2021, 5:49am

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 · February 23, 2021, 3:18pm

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 · February 25, 2021, 4:49am

@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