Run a simple sql group by query in kibana 4

Question

I want to run a simple sql group by query in kibana 4 "Discover" page. Each record in my elastic search index represent a log and has 3 columns: process_id (not unique value), log_time, log_message.

example:

process_id                log_time             log_message

----------------       --------------------       --------------------

1                        2014/12/11 01:00           msg1

1                        2014/12/11 01:10           msg2

1                        2014/12/11 01:20           msg3

2                        2014/12/11 11:00           msg4

2                        2014/12/11 11:10           msg5

I want to generate a table in kibana that looks like:

process_id         first log_time              last log_time

---------------- ------------------------       --------------------

1                    2014/12/11 01:00       2014/12/11 01:20

2                    2014/12/11 11:00       2014/12/11 11:10

In sql the query is simple: select process_id, max(log_time), min(log_time) from logs_table group by process_id

How can I run this query in Kibana? Is it possible to run the query in "Discover" page or should I create a panel (Visualize page)?

thanks.

Answer 1

I'm on Kibana 4.3, but this is possible on any version of Kibana. You need to create a Visualization panel of type Data Table.

Before that you need to make sure that you've created an index pattern for your index, such as this one, with the log_time date field as the timestamp for your index.

Then you can create your Data Table visualization and it must look like this, i.e. a split rows terms aggregation on the process_id field and then two metrics aggregation (one min and one max) on the log_time date field

Finally, your results will look like this as expected: