Logstash JDBC input Oracle settings


#1

Hi there
I thought I would share my settings that I use to help save anyone else pulling their hair out :slight_smile:

jdbc {
      # The path to our downloaded jdbc driver saved mine in /bin, driver ojdbc6.jar also works. 
      #Best location can be /vendor folder
           jdbc_driver_library => "ojdbc7.jar"
      # ORACLE Driver Class
           jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
      # ORACLE jdbc connection string to our database, ORACLE  jdbc:oracle:thin:@hostname:PORT/SERVICE
           jdbc_connection_string => "jdbc:oracle:thin:@hostname:PORT/SERVICE"
    # The user and password we wish to execute our statement as
           jdbc_user => "DBUSERNAME"
           jdbc_password => "DBPASSWORD"
   
    # our SQL query
    statement => "select * from AWESOMETABLE"

    #optional extras I use 
      type => "Database"
      tags => ["awesome", "import"]
 }

Hope that helps
Thanks


Class loading failure with Logstash 2.1.0: oracle.sql.STRUCT
(Michael Li Zhou) #2

Hey thanks! This is not so straight forward for some settings especially the driver part. I want to ask have you got the timestamp to work on this setting? I have not been able to get timestamps to match. This is off topic so if you want just message me directly.


#3

I can understand that it was not so easy to setup , I am using this to retrieve log files written by our web application import engine that go directly into Oracle. there are a few time stamps in this log that are not in a good ISO8601 format but I will be working on a Grok Pattern to take care of these.
Generally though I would try and do a grok pattern match or use Grok Debugger or Grok Matcher to test these http://grokconstructor.appspot.com/do/match


(Michael Li Zhou) #4

Yup I do the same thing. It matches as a grok pattern when I test just not while using logstash. Just a heads up if you see the error ruby and java string mismatch its because of the data type and you have to convert. I came across this problem earlier.


(Bogdan Pavel) #5

Hello, I get the error: cannot convert instance of class org.jruby.rubyobject to class java.lang.string. Any clue?

Thanks


(istvan diosi) #6

It works! :slight_smile: Thanks


(mabrouk) #7

Good evening I have a question
logstash collect the data from oracle database but with empty value
help me plz


#9

Thanks for the example, but when I am running am seeing the below error:

2017-07-11T15:32:14,388][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseConnectionError: Java::JavaSql::SQLException: Invalid Oracle URL specified>}
[2017-07-11T15:32:14,389][WARN ][logstash.inputs.jdbc ] Attempt reconnection.
[2017-07-11T15:32:14,398][WARN ][logstash.inputs.jdbc ] Failed test_connection.
[2017-07-11T15:32:14,400][ERROR][logstash.pipeline ] A plugin had an unrecoverable error. Will restart this plugin.
Plugin: <LogStash::Inputs::Jdbc jdbc_connection_string=>"jdbc:oracle:thin@//slcak349.us.oracle.com:1592:ems2971", jdbc_user=>"fusion", jdbc_password=>, jdbc_validate_connection=>true, jdbc_driver_library=>"C:\Users\ktabjul.ORADEV\Documents\sqldeveloper-4.2.0.17.089.1709-x64\sqldeveloper\jdbc\lib\ojdbc8.jar", jdbc_driver_class=>"Java::oracle.jdbc.driver.OracleDriver", statement=>"SELECT * from fusion.contacts", id=>"92a93716eec3edac55e4f28fda3966f0c979de53-1", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_e129ca99-af5f-40c5-a3d6-0a4334f96893", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts=>1, connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"C:\Users\ktabjul.ORADEV/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
Error: undefined method `close_jdbc_connection' for #Sequel::JDBC::Database:0x3f13ab08

Here is my configuration:

file: simple-out.conf

input {
jdbc {
jdbc_connection_string => "jdbc:oracle:thin@//slcak349.us.oracle.com:1592:ems2971"
jdbc_user => "fusion"
jdbc_password => "fusion"
jdbc_validate_connection => true
jdbc_driver_library => "C:\Users\ktabjul.ORADEV\Documents\sqldeveloper-4.2.0.17.089.1709-x64\sqldeveloper\jdbc\lib\ojdbc8.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
statement => "SELECT * from fusion.contacts"
}
}
output {
stdout { }
}

Please let me know what is it am doing wrong


(Saad Uddin) #10

jdbc_connection_string => "jdbc:oracle:thin@//slcak349.us.oracle.com:15926:ems2971"

you have not used the right syntax for defining service it sholud be like

jdbc_connection_string => "jdbc:oracle:thin@//slcak349.us.oracle.com:15926/ems2971"


(Jitendra Singh) #11

How would you change the select statement if you had a table name with / for example my table name is /BI0/PP and some fields also have a / name such as /BIC/PP_LOC