In this article, we will discuss how to do multiple column aggregation in kibana?

As we know Kibana is part of ELK stack and using widely into the industry. Before jumping to the article, let’s get some information about Kibana and ELK stack. 

Introduction to ELK Stack

  • The ELK Stack is a collection of three open-source products — Elasticsearch, Logstash, and Kibana. 
  • ELK Stack is mainly known for their powerful architecture for developing a centralized log data pipeline. 
  • Elasticsearch is a NoSQL database that is based on the Lucene search engine.
  • Logstash is a log pipeline tool that accepts inputs from various sources, executes different transformations, and exports the data to various targets. 
  • Kibana is a visualization layer that works on top of Elasticsearch.

Now let’s move to the main topic. 

How to do Multiple Column Aggregation In Kibana

I am going to divide this process into two steps:-

  1. Pre-Check Step 
    In this step, we have to check our script is working fine or not. 
  2. Implement Step 
    In this step, we have to implement our script for using purpose. 

Pre-requisites for this process:-

  1. Elasticseach should be in running status 
  2. Kibana should be in running status
    Note:- In my step, I am using Elasticsearch 6.3 and Kibana 6.3.

Now let’s first understand the scenario:-

I have one index name as “cars” in which more than 10 fields / Columns are there. I am going to perform a simple multiply aggregation on two columns such as

  •  “door_count” and “engine_power”.
  • Both fields/columns data type is “NUMBER”.

Now let’s move to our step 1  which is Pre-Check Step

Pre-Check Step:-

  1. Open Kibana URL
    For Open, enter localhost:5601 into your browser to open the kibana interface.
  2. Open Devtools -> Console. 
    Click on “Dev Tools” and then “Console”.
  3. Write below script
    GET /cars/_search
    {
    "query": {
    "match_all": {}
    },
    "script_fields": {
    "sf_name1": {
    "script":{
    "lang": "painless",
    "source": "doc['door_count'].value * doc['engine_power'].value"
    }
    }
    }
    }

    Here I am using =>
    my index name “cars”
    script name “sf_name1”
    script language type “painless”. 
    Sum aggregation between door_count and engine_power

  4. Run this query
    You will get the calculated value which you can check. I am getting below data which is correct from my side.

    {
    "took": 31,
    "timed_out": false,
    "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
    },
    "hits": {
    "total": 3552913,
    "max_score": 1,
    "hits": [
    {
    "_index": "cars",
    "_type": "sold_cars",
    "_id": "Qc6Y7mQB-3RCNeqYu7Ph",
    "_score": 1,
    "fields": {
    "sf_name1": [
    336
    ]
    }
    },
    {
    "_index": "cars",
    "_type": "sold_cars",
    "_id": "Sc6Y7mQB-3RCNeqYu7Ph",
    "_score": 1,
    "fields": {
    "sf_name1": [
    352
    ]
    }
    },
    {
    "_index": "cars",
    "_type": "sold_cars",
    "_id": "S86Y7mQB-3RCNeqYu7Ph",
    "_score": 1,
    "fields": {
    "sf_name1": [
    400
    ]
    }
    }

It means my script is working fine. Now time for implementation. 

Implement Step:-

  1. Open Index Pattern
    For Open Index Pattern, you have to click on “Management” then select your index. 
    In my case, my index name is “cars“, I will select “cars”. 
  2. Open Scripted Field and define a script
    In the index pattern page, click on “Scripted field“,  then click on “Add Scripted Field” and provide all details as per your pre-check steps and click on “Create field”.
    In my case, I filled below information:-

    Name: sf_name1
    Lang: painless
    Type: Number
    Format: Number
    Script: doc['door_count'].value * doc['engine_power'].value

    This will create script field successfully.

  3. Check Scripted Field 
    For cross check, Click on “Discover” and Select your index. and You will see your scripted field. 

Yuppiee, finally you can able to create a script and able to use script field as a normal field into your aggregation and analysis step. 


Conclusion

I hope, you learn how to do multiple column aggregation in kibana. In case of any confusion, you can comment down and I will try my best to reply as soon as possible.

For other tutorials, related to ELK Stack, please visit my website 

Share.

About Author

1 Comment