# How to join fields from multi events into single event?

**URL:** <https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364>\
**Category:** Logstash\
**Created:** [November 3, 2017, 9:04pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364 "2017-11-03T21:04:35Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![djgerhab](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/djgerhab/32/23793_2.png) [@djgerhab](https://discuss.elastic.co/u/djgerhab)\
**Post date:** [November 3, 2017, 9:04pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/1 "2017-11-03T21:04:36Z")

</div>

Hi, I feel like my issue should be a common one, however I have spent days trying to find the answer to no avail...

Here is what I am trying to accomplish: I have data from 2 separate databases that share a common primary key. I am using the JDBC input plugin to grab the data events. I need to combine the fields together based on the id and then output it to elasticsearch. How can I accomplish this? So far I have the following structure in the logstash conf:

```
input {
    jdbc { datasource1... }
}

input {
   jdbc { datasource2... }
}

filter {
  aggregate {
     (using example #4 from filter plugin page)
  }
}

output {
  elasticsearch { ... }
}

```

The [aggregate filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html) will only work if my db results were ordered in such a way which the same primary key was right after one another, and using 2 datasources doesn't work that way. I wish I just didn't need the filter altogether and there was some join that could be done before outputing.

Thank you in advance for looking into this!

- Dan

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [November 4, 2017, 8:59pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/2 "2017-11-04T20:59:30Z")

</div>

Logstash isn't stateful in a way that can handle this.  
You will need to do two steps, grab the data from DB1 and put it into Elasticsearch in a temp index, then get the second DB dataset and do a lookup in the temp index to add the values from the first set.

---

<div class="post-metadata">

**Author:** ![djgerhab](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/djgerhab/32/23793_2.png) [@djgerhab](https://discuss.elastic.co/u/djgerhab)\
**Post date:** [November 7, 2017, 10:06pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/3 "2017-11-07T22:06:06Z")

</div>

Thanks for the suggestion, I applied that today and is a good workaround. I may end up executing a stored procedure (if possible) instead and do the join in there, but for now this solves my issue =)

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [November 7, 2017, 10:22pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/4 "2017-11-07T22:22:22Z")

</div>

Would you be willing to share the (relative) config sections? I am sure someone else will find it useful! 😃

---

<div class="post-metadata">

**Author:** ![djgerhab](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/djgerhab/32/23793_2.png) [@djgerhab](https://discuss.elastic.co/u/djgerhab)\
**Post date:** [November 10, 2017, 8:48pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/5 "2017-11-10T20:48:13Z")

</div>

Sure thing, this was used for storing company data

Config 1:

```
input {
    jdbc {
        id => "internal_data_plugin"
        jdbc_driver_library => "/etc/logstash/resources/drivers/sqljdbc42.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => (server info)
        jdbc_user => (username)
        jdbc_password => (password)
        schedule => "0 0 * * *"
        statement_filepath => "/etc/logstash/resources/statements/internal-data.sql"
        connection_retry_attempts => 3
        connection_retry_attempts_wait_time => 10
    }
}

output {
    elasticsearch {
        hosts => ["127.0.0.1"]
        user => (username)
        password => (password)
        index => "temp"
        document_type => "company"
        document_id => "%{companyId}"
    }
}

```

Config 2:

```
input {
    jdbc {
        id => "universal_data_plugin"
        jdbc_driver_library => "/etc/logstash/resources/drivers/sqljdbc42.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_connection_string => (server info)
        jdbc_user => (username)
        jdbc_password => (password)
        schedule => "0 1 * * *"
        statement_filepath => "/etc/logstash/resources/statements/universal-data.sql"
        connection_retry_attempts => 3
        connection_retry_attempts_wait_time => 10
    }
}

filter {
    elasticsearch {
        hosts => ["127.0.0.1"]
        user => (username)
        password => (password)
        index => "temp"
        query => "companyId:%{companyId}"
        fields => {
            "transactionDate" => "transactionDate"
            "totalAssets" => "totalAssets"
        }
    }
}

output {
    elasticsearch {
        hosts => ["127.0.0.1"]
        user => (username)
        password => (password)
        index => "project"
        document_type => "company"
        document_id => "%{companyId}"
        template => "/etc/logstash/resources/templates/project-template.json"
        template_name => "project"
        template_overwrite => "true"
    }
}
```

---

<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:** [December 8, 2017, 8:48pm UTC](https://discuss.elastic.co/t/how-to-join-fields-from-multi-events-into-single-event/106364/6 "2017-12-08T20:48:35Z")

</div>

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