Logstash is not writing latest value in jdbc Sincedb

Elastic Stack Logstash
1

I am using 5.x version of Logstash and 7.x version of Elasticsearch and Kibana, the issue I am facing is even after setting the path for the sincedb, logstash is not writing the timestamp till the point reading has been complete. Every time it Starts reading the whole data.

I am using the mentioned below configuration.

input{
    jdbc{
        jdbc_driver_library => "/usr/share/logstash/bin/mysql-connector-java-8.0.16.jar"
        jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://10.32.5.51:3307/sakila?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"
        jdbc_user => "root"
        jdbc_password => "root"
        statement => "SELECT * FROM sakila.city WHERE updatedon > :sql_last_value"
        type =>"dailysummary"
        # schedule => "*/5 * * * *"
        use_column_value => true
        tracking_column => updatedon
        record_last_run => true
        last_run_metadata_path => "/usr/share/logstash/bin/since"
    }
}
filter{
    
}

output {
    # stdout {
    #     codec=>rubydebug
    # }
	elasticsearch {
        hosts=> "localhost:9200"
        index=> "dailysummary-%{+YYYY-MM-dd}"  
    }
}
2

Have this in your filter section to get timestamp:

  date {
    match => ["log-datestamp", "YYYY-MM-dd HH:mm:ss,SSS"]
    target =>  "@timestamp"
    timezone => "UTC"
  }       
  date {
    match => ["log-datestamp", "YY-MM-dd HH:mm:ss,SSS"]
    target =>  "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-datestamp", "ISO8601"]
    target =>  "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-epoch", "UNIX"]  
    target =>  "@timestamp"
    timezone => "UTC"
  }    
  date {
    match => ["log-epoch", "UNIX_MS"]
    target =>  "@timestamp"
    timezone => "UTC"
  }
3

why duplicate clauses (1st 2)?

4

It is not duplicate, one starts with YYYY and the other starts with YY.

5

Oops, silly human

6

I'm here on this thread because my 7.0.1 Logstash/jdbc/since tasks are not working. They're creating duplicate Elasticsearch documents from a single SQL row, even after incorporating the date filters suggested above. Logstash keeps pulling records it already pulled; Kibana's Discovery pane shows the same row arriving repeatedly, but it only exists once in the DB.

My conf:

input {
jdbc {
jdbc_driver_library => "/usr/share/java/mysql-connector-java.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://192.168.40:3306/firefly"
jdbc_user => "***"
jdbc_password => "**"
statement => "SELECT * FROM tst_log WHERE timestamp >= :sql_last_value"
use_column_value => true
tracking_column => "timestamp"
tracking_column_type => "timestamp"
schedule => " * * * *"
last_run_metadata_path => "/usr/share/logstash/.log_logstash_jdbc_last_run"
}
}

filter {
date {
match => ["log-datestamp", "YYYY-MM-dd HH:mm:ss,SSS"]
target => "@timestamp"
timezone => "UTC"
}
<snipped to meet limits, but matches EZProgramming's suggestion>

7

More info. The journal shows repeated instances of this line:

Jul 09 09:34:04 amos.nexus-tech.local logstash[27024]: [2019-07-09T09:34:04,552][INFO ][logstash.inputs.jdbc ] (4.378876s) SELECT * FROM vw_ts_sno_events WHERE stamp >= '2019-07-09 08:54:02'

Only 1 record was created in the SQL database since 00:00:00 July 9, 2019, at precisely 8:54:02; but Kibana shows 39 records received on July 9 in the Discovery panel. Since starting this jdbc job 24 hours ago, it has inflated 16,514 SQL rows into 17,702 Elastic documents.

8

Using >= means it will repeatedly fetch the most recent row.

9

Thanks! I will change immediately, restart, and see what happens over a couple of hours.

10

I can tell it's fixed already. The Kibana count for today went from 40+ to 1. Thanks for catching my mistake.

11

@Badger This also made my life easy and resolved my issue as well. Filter part ha made the things align, Further the repetitions are also removed. THANKS EVERYONE !!!

@bayardk I was also facing the issue of repetitions but now I am getting totally accurate data.

12

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