# Logstash jdbc config file with filter

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675>\
**Category:** Logstash\
**Created:** [February 10, 2020, 9:41pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675 "2020-02-10T21:41:09Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\
**Post date:** [February 10, 2020, 9:41pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675/1 "2020-02-10T21:41:10Z")

</div>

Hey,

I'm new with logstash jdbc plugin and somehow I set up the migration of data from MySQL database to Elasticsearch via Logstash by using this configuration:

```
input {
  jdbc {
    jdbc_driver_library => "/usr/share/java/mysql-connector-java-5.1.45.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/testdb"
    jdbc_user => user
    jdbc_password => password
    statement => "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;"
    schedule => "* * * * * *"
  }
}
output {
  elasticsearch {
    document_id => "%{id}"
    document_type => "doc"
    index => "test2"
    hosts => ["http://10.0.2.9:9200"]
  }
  stdout{
  codec => rubydebug
  }
}

```

It shows me which user is active at the current in Kibana but it changes the content of stored data because the log message has the same id number. You can see it in the pictures:

 ![first_LI](https://us1.discourse-cdn.com/elastic/original/3X/b/b/bb1438f6e902d0bd0bea18dd4a4202691916d3a9.jpeg)

 ![second_LI](https://us1.discourse-cdn.com/elastic/original/3X/0/4/04e691d539a1b25a3f4c0db1a78930ffeed7a693.jpeg)

So until a particular user doesn't log out it changes the databse the user is using with the same id so it is not possible to search what databse was user using in the past.

How can I set Logstash to store each record with different id or just timestamp so it would be possible to see the previous records...

I tried to explain it as much as I could so if something is unclear just let me know and I try to clarify 😃

Thanks for any advices!!

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [February 10, 2020, 10:06pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675/2 "2020-02-10T22:06:14Z")

</div>

you can create your own document\_id

```
filter{
   mutate { add_field => { "doc_id" => "%{id}-%{user}-%{@timestamp}" } }
}

output {
   document_id => "%{doc_id}"
}

```

this will create new id = 4-root-timestamp

and it will not overwrite old record.

---

<div class="post-metadata">

**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\
**Post date:** [February 10, 2020, 10:36pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675/3 "2020-02-10T22:36:57Z")

</div>

Thank you very much for help! It does what I wanted )))

Out of curiosity do you have any idea how to set up the same migration mechanism for MariaDB ? I tried it by using the same config file like I showed in this post but there is some problem with jdbc Driver class and maybe library...

---

<div class="post-metadata">

**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\
**Post date:** [February 12, 2020, 2:04pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675/4 "2020-02-12T14:04:23Z")

</div>

> [@Vladpov](#):
>
> Driver class and maybe library...

never worked with MariaDB. sorry

---

<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:** [March 11, 2020, 2:04pm UTC](https://discuss.elastic.co/t/logstash-jdbc-config-file-with-filter/218675/5 "2020-03-11T14:04:27Z")

</div>

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