# JDBC Input - New Events only

**URL:** <https://discuss.elastic.co/t/jdbc-input-new-events-only/99964>\
**Category:** Logstash\
**Created:** [September 10, 2017, 7:17am UTC](https://discuss.elastic.co/t/jdbc-input-new-events-only/99964 "2017-09-10T07:17:32Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![karnamonkster](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/karnamonkster/32/67266_2.png) [@karnamonkster](https://discuss.elastic.co/u/karnamonkster)\
**Post date:** [September 10, 2017, 7:17am UTC](https://discuss.elastic.co/t/jdbc-input-new-events-only/99964/1 "2017-09-10T07:17:32Z")

</div>

Hello,  
I am running ELK Stack - 5.4.1 with JDBC Input plugin to fetch data from an ORACLE database table  
My data does not have any unique ID  
Even the Timestamp (date\_and\_time) brings multiple events at same time.  
Hence if i run the query every minute with SQL\_LAST\_VALUE parameter to date\_and\_time, i missout on some of the events from the table.

Here are the fields

```
 @timestamp September 10th 2017, 10:49:21.701
t @version 1
t _id AV5qmlxj4jPABV64Xopk
t _index eventlist-2017.09.10
# _score - 
t _type 111
# alarm_limit_high - 
# alarm_limit_low - 
# archive_class 0
t area Zone 7 - LOCATION
t area_code LUSL
# area_id 7
t b1 814:LOCATION3
t b2 xxxM
t b3 .
t b4 RTU
t b5 RTU TEMP HIGH ALARM
# client 0
# color 0
# comment_nbr 0
# criticity 2
 date_and_time September 10th 2017, 10:48:21.161
# date_and_time_utc 1,505,026,101.161
# dec_places - 
t dimension - 
t dpadr MLXXYYSS
# event_id 72,556
t event_text 814:ALROWAIYAH 3|R130M|.|RTU|RTU TEMP HIGH ALARM||On| 
 gentime November 13th 2014, 13:01:50.000
# index_nbr 0
# ipid 10,055,639
t iptype ML
# it 1,512
# latitude 25.16
 location LAT.LON
# longitude 55.5
# max_value - 
# min_value - 
# nbr 84
# part 16
? pis - 
# plant 128
# points 1
t prjtxt1t - 
# profile_type 0
t pvname RSCAPSFSD:DI
# rtu 130
 rtu_time September 10th 2017, 10:48:20.108
# rtu_time_utc 1,505,026,100.108
t state_one On              
t state_zero Off             
? tc_adr_asdu - 
? tc_adr_io - 
t text RXXM
# type 111
# upper_ipid 10,030,541
# value 1
# warning_limit_high - 
# warning_limit_low - 

```

My query is

```
jdbc {
           jdbc_driver_library => "ojdbc7.jar"
           jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
           jdbc_connection_string => "jdbc:oracle:thin:@hostname:PORT/SERVICE"
           jdbc_user => "DBUSERNAME"
           jdbc_password => "DBPASSWORD"
      statement => "select * from events WHERE date_and_time> :sql_last_value"
      schedule => "* * * * *"
      use_column_value => true
      tracking_column => date_and_time
}
```

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [September 10, 2017, 3:51pm UTC](https://discuss.elastic.co/t/jdbc-input-new-events-only/99964/2 "2017-09-10T15:51:55Z")

</div>

The statement **must** return results **ordered** by `date_and_time`. If the results are not ordered, then the very last records date\_and\_time value may not be the most recent one ingested - then the next run will return records newer causing duplicates.

Check the datatype of the `date_and_time`. If it is a timestamp (DB datatype) set the `tracking_column_type` to "timestamp" otherwise you may have to cast it in the statement.

---

<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:** [October 8, 2017, 3:52pm UTC](https://discuss.elastic.co/t/jdbc-input-new-events-only/99964/3 "2017-10-08T15:52:02Z")

</div>

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