Kibana: How to get raw data and aggregate in KQL in a serial fashion?

I was looking for equivalent of splunk query (i.e. get raw data , then do aggregation on top of that)

index=some_data | stats count by hostname

The above will aggregate all the data by hostname and shows the data in nice looking GUI table && charts.

How to do the above equivalent in KQL/Kibana?
(PS: I don't want to use the mouse clicks to do aggregation but everything as a query)

Hi @kelk,

Kibana's API is evolving, so not everything may be possible without mouse clicks today. This is changing though, so keep an eye out for updates in this space.

In addition to Elasticsearch's DSL, we also offer SQL support, which integrates nicely with Canvas for visualizations: https://www.elastic.co/what-is/elasticsearch-sql

I'm not familiar with Splunk's query syntax, but a similar SQL query might look something like this:

   SELECT hostname, stats
   FROM some_data
   GROUP BY hostname

thanks Larry for the peek into future. Really expecting to have Kibana API to do all these and then Elastic will be to the moon

With SQL, can we pipe things (in series) and do modifications on data ? The question I asked is more on the ability to pipe(serial) to do more actions on processed data..

So in the SQL example

    SELECT hostname, stats
       FROM some_data
       GROUP BY hostname
    | do some regex
    | do lookup with external file
    | do concatenate two fields

basically in a serial fashion

Ah thanks for clarifying. Canvas's expression language supports a lot of this, and it comes with a number of built-in functions:

Expression Language

Function Reference

I believe it's also possible to create your own functions as a Kibana Plugin, but I'm not seeing any public documentation on that just yet (this is also something we're working hard on as part of our new architecture: https://www.elastic.co/blog/introducing-a-new-architecture-for-kibana)

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.