# Sending Data From Oracle to Elastic **URL:** \ **Category:** Logstash\ **Created:** [June 29, 2022, 11:22am UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460 "2022-06-29T11:22:20Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Dhruvin.patel001](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Dhruvin.patel001](https://discuss.elastic.co/u/Dhruvin.patel001)\ **Post date:** [June 29, 2022, 11:22am UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/1 "2022-06-29T11:22:20Z")
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. ---
**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\ **Post date:** [June 29, 2022, 3:46pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/2 "2022-06-29T15:46:42Z")
> [@Dhruvin.patel001](#): > > I am getting the data until my current time but not able to get updated data from database. 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`? ---
**Author:** ![Dhruvin.patel001](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Dhruvin.patel001](https://discuss.elastic.co/u/Dhruvin.patel001)\ **Post date:** [June 29, 2022, 6:09pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/3 "2022-06-29T18:09:54Z")
1. Yes 2. No ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [June 29, 2022, 6:43pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/4 "2022-06-29T18:43:25Z")
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) ---
**Author:** ![Dhruvin.patel001](https://avatars.discourse-cdn.com/v4/letter/d/74df32/32.png) [@Dhruvin.patel001](https://discuss.elastic.co/u/Dhruvin.patel001)\ **Post date:** [June 29, 2022, 6:51pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/5 "2022-06-29T18:51:26Z")
@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 ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [June 29, 2022, 7:08pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/6 "2022-06-29T19:08:47Z")
> [@Dhruvin.patel001](#): > > 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') I am confuse as your output of logstash says otherwise. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 27, 2022, 7:09pm UTC](https://discuss.elastic.co/t/sending-data-from-oracle-to-elastic/308460/7 "2022-07-27T19:09:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.