15

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.

No Idea For Name
  • 11,411
  • 10
  • 42
  • 70
PMOPMO
  • 151
  • 1
  • 1
  • 4

1 Answers1

15

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.

enter image description here

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

enter image description here

Finally, your results will look like this as expected:

enter image description here

Val
  • 207,596
  • 13
  • 358
  • 360
  • Dear Val, please kindly answer my question, max can be used to choose top 1 value, but how to choose top n value. like in sql we might do : choose top 5 value ``SELECT * FROM table ORDER BY column DESC LIMIT 5`` – yuliansen Sep 29 '20 at 07:32
  • 1
    @yuliansen Please create a new question (maybe referencing this one) which explains your context and needs – Val Sep 29 '20 at 07:33
  • please kindly check out ``https://stackoverflow.com/questions/64115037/query-top-n-value-in-kibana`` – yuliansen Sep 29 '20 at 07:43