JDBC Input - New Events only


(karnamonkster) #1

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
}

(Guy Boertje) #2

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.


(system) #3

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