I'm going to use jdbc plugin to get data from oracle database,below is my sample data:
OP_TYPE OP_DATE OBJ_NAME LAG HOSTNAME GROUP_NAME RECORDLENGTH TIMESTAMP FILESEQNO FILERBA ID
---------- ------------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ----------
INSERT 23-4-16 SOURCE.T1 4 oracle02 REPTGT 22 23-4-16 ########## ########## 101
INSERT 23-4-16 SOURCE.T1 4 oracle02 REPTGT 23 23-4-16 ########## ########## 102
Column ID is an auto-increment column and this value will be saved to determine where to start.but if i set use_column_value to true,sql_last_value will be 0, set use_column_column_value to false,sql_last_value will be saved as a timestamp.
Anyone could help me to save my actual ID value to sql_last_value?
below is my currently configuration
input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@114.94.154.172:1521/orcl"
jdbc_user => "ogg"
jdbc_password => "ogg"
jdbc_driver_library => "/home/logstash/ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
clean_run => false
record_last_run => true
statement => "select ID,OP_TYPE,OP_DATE,OBJ_NAME,LAG,HOSTNAME,GROUP_NAME,RECORDLENGTH,TIMESTAMP,FILESEQNO,FILERBA from ogg.ogg_history where ID>:sql_last_value"
use_column_value => true
tracking_column => ID
last_run_metadata_path => "/tmp/logstash-oradb.lastrun"
schedule => "* * * * *"
}
}
#mutate { remove_field => [ "force_matching_signature" ] }
filter {
# Set the timestamp to that of the ASH sample, not current time.
mutate { convert => [ "sample_time" , "string" ]}
date { match => ["sample_time", "ISO8601"]}
}
output {
stdout { codec => rubydebug }
elasticsearch {hosts=>"xx.xx.xx.xx"}
}
sql_last_value:
[logstash@iZ23qrdz9ieZ tmp]$ cat logstash-oradb.lastrun
--- 0
[logstash@iZ23qrdz9ieZ tmp]$ cat logstash-oradb.lastrun
--- 2016-04-23 07:08:00.012000000 Z