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

**URL:** <https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930>\
**Category:** Kibana\
**Created:** [July 5, 2020, 5:07pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930 "2020-07-05T17:07:31Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kelk](https://avatars.discourse-cdn.com/v4/letter/k/13edae/32.png) [@kelk](https://discuss.elastic.co/u/kelk)\
**Post date:** [July 5, 2020, 5:07pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930/1 "2020-07-05T17:07:31Z")

</div>

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

```auto
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)

---

<div class="post-metadata">

**Author:** ![Larry\_Gregory](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/larry_gregory/32/34969_2.png) [@Larry\_Gregory](https://discuss.elastic.co/u/Larry_Gregory)\
**Post date:** [July 6, 2020, 2:18pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930/2 "2020-07-06T14:18:45Z")

</div>

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](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:

```sql
   SELECT hostname, stats
   FROM some_data
   GROUP BY hostname

```

---

<div class="post-metadata">

**Author:** ![kelk](https://avatars.discourse-cdn.com/v4/letter/k/13edae/32.png) [@kelk](https://discuss.elastic.co/u/kelk)\
**Post date:** [July 6, 2020, 3:53pm UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930/3 "2020-07-06T15:53:02Z")

</div>

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

```auto
    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

---

<div class="post-metadata">

**Author:** ![Larry\_Gregory](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/larry_gregory/32/34969_2.png) [@Larry\_Gregory](https://discuss.elastic.co/u/Larry_Gregory)\
**Post date:** [July 7, 2020, 12:23am UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930/4 "2020-07-07T00:23:12Z")

</div>

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](https://www.elastic.co/guide/en/kibana/current/canvas-expression-lifecycle.html)

[Function Reference](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html)

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](https://www.elastic.co/blog/introducing-a-new-architecture-for-kibana))

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 4, 2020, 12:23am UTC](https://discuss.elastic.co/t/kibana-how-to-get-raw-data-and-aggregate-in-kql-in-a-serial-fashion/239930/5 "2020-08-04T00:23:13Z")

</div>

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