Sending Data From Oracle to Elastic

Elastic Stack Logstash
1

I am trying to send oracle events to Elasticsearch. In oracle DB, we have create_date column as primary key.

Here is the format of create date:-
22-JUL-19 02.22.02.918000000 PM
22-JUL-19 02.22.03.325000000 PM
22-JUL-19 02.22.05.271000000 PM
22-JUL-19 02.41.26.428000000 PM

logstash Config:-
input {
jdbc {
jdbc_driver_library => "/ojdbc8-21.1.0.0.jar"
jdbc_driver_class => "Java::oracle.jdbc.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@:2056/"
jdbc_user => ""
jdbc_password => ""
schedule => " * * * "
tracking_column => create_date
tracking_column_type => "timestamp"
record_last_run => true
use_column_value => true
statement => "select * from table where create_date >:sql_last_value and create_date > to_date('06/29/2022 00:00:00', 'mm/dd/yyyy HH24:mi:SS')"
jdbc_default_timezone => "America/Los_Angeles"
}
}
output
{
elasticsearch {
hosts => [""]
index => "events-%{+YYYY.MM.dd}"
}
}

so I want to send events after 29th June and all updated events after db gets updated.
I am struggling with sql_last_value and after I run above logstash I am getting the data until my current time but not able to get updated data from database.

  1. How can I convert sql_last_value to DB timestamp so I can get updated events as well?
  2. After above step is done, How can I convert create_date in CST timezone so in Elasticsearch I can get create_date as CST?

Logstash Logs:-
[2022-06-28T13:43:08,508][INFO ][logstash.inputs.jdbc ][main][8fa255d6b891cb28262f8372bc473947b9d32fcaf61aea48557b6a3b8ed48210] (7.073158s) select * from * where create_date >TIMESTAMP '2022-06-28 09:38:02.661000 -07:00' and create_date > to_date('06/28/2022 00:00:00', 'mm/dd/yyyy HH24:mi:SS')

It's stuck at this timestamp and each time It is querying for same timestamp so sql_last_value is not updating here.

Thanks in Advance.

2
  1. Are you expecting future dated entries in the table ?
  2. Have you also used last_run_metadata_path parameter to store sql_last_run?
3
  1. Yes
  2. No
4

SQL seems wrong
create_date > 2022-06-08 and create_date > 06/28/2022 which is not going to match

it should be
create_date > 2022-06-08 and create_date < 06/28/2022 ( to get data between these two date, not including this date)

5

@elasticforme I have run this script today so sql_last_value should be greater than 06/28/2022. The condition is that I want data starting from 28th which will be fulfilled by create_date > 06/28/2022 and continuously updated records with create_date > :sql_last_value

6

I am confuse as your output of logstash says otherwise.

7

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