# Reading logs from DB - Avoiding of duplicate records

**URL:** <https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996>\
**Category:** Logstash\
**Created:** [June 23, 2019, 9:06am UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996 "2019-06-23T09:06:40Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 9:06am UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/1 "2019-06-23T09:06:40Z")

</div>

Hello,  
I would like to read LOGS from database from table logs. These logs I would like to send using logstash to syslog server.

I would to ensure sending events only once:

- I mark events that are going to be sent
- I select marked events _(during this time, some events can arrive and these events are not marked)_
- These sevents I can sent to remote syslog server
- I can delete logs from table that had already been sent

**1) Update some records (marking)**

```
UPDATE logs SET marked_by_logstash = true WHERE marked_by_logstash = false ;

```

**2) Select data from database based on marks**

```
SELECT * FROM logs WHERE marked_by_logstash = true ;

```

**3) After marking and reading - Start with deleting unnecessary rows**

```
DELETE FROM logs WHERE marked_by_logstash = true ;

```

I tried to do with 3x jdbc in input section in Logstash but I found out that **executing input JDBC sequentially is not possible** - [here](https://discuss.elastic.co/t/how-to-run-queries-from-multiple-jdbc-inputs-sequentially/77226/4).

**Is it possible to read rows from DATABASE only once to avoid sending duplicate records?**  
Do you have any idea how to do?  
Vasek

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 23, 2019, 9:42am UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/2 "2019-06-23T09:42:46Z")

</div>

> [@vasek](#):
>
> Is it possible to read rows from DATABASE only once to avoid sending duplicate records?

It is [possible](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) if there is a column with either a date or a sequence.

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 9:52am UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/3 "2019-06-23T09:52:41Z")

</div>

Thank you @Badger !

I am guessing that this can be achieved with these 5 parameters. Am I right?

```
use_column_value => true
tracking_column => "timestamp-of-generating-event"
tracking_column_type => "timestamp"
last_run_metadata_path => "/etc/logstash/jdbc_last_run_metadata_path/test"
record_last_run => true
```

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 12:21pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/4 "2019-06-23T12:21:42Z")

</div>

I do know why, but events are still duplicated.

**Logstash pipeline configuration file**

```
input {
  jdbc {
    jdbc_validate_connection => true
    jdbc_driver_library => "/etc/logstash/java-libs/mariadb-java-client-2.4.2.jar"
    jdbc_driver_class => "Java::org.mariadb.jdbc.Driver"
    jdbc_connection_string => "jdbc:mariadb://10.88.88.150:3306/test_database"
    jdbc_user => " *****"
    jdbc_password => " *******"
    schedule => "*/1 * * * *"
    statement => "SELECT DATE_FORMAT(timestamp, '%Y-%m-%dT%TZ') AS timestamp_generating_event, severity, message from logs ORDER BY timestamp_generating_event ASC"
    sql_log_level => "debug"
    record_last_run => true
    last_run_metadata_path => "/etc/logstash/jdbc_last_run_metadata_path/mariadb"
    use_column_value => true
    tracking_column => "timestamp_generating_event"
    tracking_column_type => "timestamp"
  }
}

```

Logstash is querying to database every minute.

**Generating events to database**  
Every second I am generating row (event) to database.

**Observing last\_run**  
cat /etc/logstash/jdbc\_last\_run\_metadata\_path/mariadb  
now file is empty

\*\*\* **What happend** \*\*\*  
I generated 8 events to database:

```
generating 1 event - 2019-06-23T14:10:50.750
generating 2 event - 2019-06-23T14:10:51.761
generating 3 event - 2019-06-23T14:10:52.771
generating 4 event - 2019-06-23T14:10:53.781
generating 5 event - 2019-06-23T14:10:54.793
generating 6 event - 2019-06-23T14:10:55.805
generating 7 event - 2019-06-23T14:10:56.818
generating 8 event - 2019-06-23T14:10:57.829

```

I checked this records in database:

```
SELECT DATE_FORMAT(timestamp, '%Y-%m-%dT%TZ') AS timestamp_generating_event, severity, message from logs ORDER BY timestamp_generating_event DESC"

```

Output:

```
timestamp_generating_event	severity message
2019-06-23T14:10:57Z info-8 obsah zpravy 8
2019-06-23T14:10:56Z info-7 obsah zpravy 7
2019-06-23T14:10:55Z info-6 obsah zpravy 6
2019-06-23T14:10:54Z info-5 obsah zpravy 5
2019-06-23T14:10:53Z info-4 obsah zpravy 4
2019-06-23T14:10:52Z info-3 obsah zpravy 3
2019-06-23T14:10:51Z info-2 obsah zpravy 2
2019-06-23T14:10:50Z info-1 obsah zpravy 1

```

Based on schedule statement in jdbc input plugin

```
schedule => "*/1 * * * *"

```

Logstash performed the query:

```
SELECT DATE_FORMAT(timestamp, '%Y-%m-%dT%TZ') AS timestamp_generating_event, severity, message from logs ORDER BY timestamp_generating_event ASC

```

So I checked last\_run file:

```
cat /etc/logstash/jdbc_last_run_metadata_path/mariadb
--- 2019-06-23 14:10:57.000000000 +00:00

```

Logstash insert 8 documents to Elasticsearch index. That's ok.

Now Logstash - jdbc input plugin - is waiting (1 minute) to next round .  
I tried to generate 4 events more.

```
generating 9 event - 2019-06-23T14:11:45.230
generating 10 event - 2019-06-23T14:11:46.239
generating 11 event - 2019-06-23T14:11:47.248
generating 12 event - 2019-06-23T14:11:48.257

```

When 1 minute elapsed, logstash started with query to database **again.**

In this time, content of database was:

```
timestamp_generating_event	severity message
2019-06-23T14:11:48Z info-12 obsah zpravy 12
2019-06-23T14:11:47Z info-11 obsah zpravy 11
2019-06-23T14:11:46Z info-10 obsah zpravy 10
2019-06-23T14:11:45Z info-9 obsah zpravy 9
2019-06-23T14:10:57Z info-8 obsah zpravy 8
2019-06-23T14:10:56Z info-7 obsah zpravy 7
2019-06-23T14:10:55Z info-6 obsah zpravy 6
2019-06-23T14:10:54Z info-5 obsah zpravy 5
2019-06-23T14:10:53Z info-4 obsah zpravy 4
2019-06-23T14:10:52Z info-3 obsah zpravy 3
2019-06-23T14:10:51Z info-2 obsah zpravy 2
2019-06-23T14:10:50Z info-1 obsah zpravy 1

```

Content of last\_run:

```
cat /etc/logstash/jdbc_last_run_metadata_path/mariadb     
--- 2019-06-23 14:11:48.000000000 +00:100: 

```

And Index had 20 document. It is 8 + 12.  
Every round logstash adds 12 documents to ES index again.

**What am I doing wrong? Could you please help me?**

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 23, 2019, 1:00pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/5 "2019-06-23T13:00:33Z")

</div>

> [@vasek](#):
>
> What am I doing wrong?

You need a WHERE clause in the query that references sql\_last\_value (which is a parameter populated from the last\_run\_metadata). The documentation has an [example](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_predefined_parameters).

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 1:22pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/6 "2019-06-23T13:22:01Z")

</div>

It works. Awesome. You made my day! Thank you @Badger

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 1:40pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/7 "2019-06-23T13:40:23Z")

</div>

Just one additional question:  
Is it possible to preserve **milliseconds**? 15:36:12 **.459**

```
cat /etc/logstash/jdbc_last_run_metadata_path/mariadb                                                                                                               
--- 2019-06-23 15:36:12.000000000 +02:00
```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [June 23, 2019, 1:44pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/8 "2019-06-23T13:44:49Z")

</div>

> [@vasek](#):
>
> Is it possible to preserve **milliseconds**?

It is saving the last value it extracted from the database. If that includes milliseconds I would expect it to save them.

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 23, 2019, 1:59pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/9 "2019-06-23T13:59:40Z")

</div>

You are right. It works fine. Thank you.

---

<div class="post-metadata">

**Author:** ![Miguel1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/miguel1/32/82094_2.png) [@Miguel1](https://discuss.elastic.co/u/Miguel1)\
**Post date:** [June 24, 2019, 10:56am UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/10 "2019-06-24T10:56:13Z")

</div>

In addition, if you want to avoid duplicates you can use [filter-fingerprint](https://www.elastic.co/guide/en/logstash/current/plugins-filters-fingerprint.html) hashing the concatenation of the fields you consider and use the result as id ([document\_id](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-document_id)). Or just use a primary key as id (for a better distribution, shard routing by default uses the id, you can use figerprint to MUMUR3 with the primary key).

---

<div class="post-metadata">

**Author:** ![vasek](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vasek/32/136636_2.png) [@vasek](https://discuss.elastic.co/u/vasek)\
**Post date:** [June 26, 2019, 3:13pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/11 "2019-06-26T15:13:53Z")

</div>

Hi @Miguel1 thank you for tip. The filter-fingerpriting can be very useful If we deciced **to store data to Elasticsearch.** In case of [sending data to remote syslog](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996) I think it cannot help.

---

<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:** [July 24, 2019, 3:14pm UTC](https://discuss.elastic.co/t/reading-logs-from-db-avoiding-of-duplicate-records/186996/12 "2019-07-24T15:14:05Z")

</div>

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