JDBC

Installation

Getting the DB jar in place

The JDBC plug-in ships with logstash so no installation of that is needed. However, you do need the JDBC driver for the DB in question. Here's an example for DB2, for which you can get the jar from either the server itself or the DB2 fix-pack associated with the DB Version you're running. The elasticsearch docs say to just put it in your path. I've put it in the logstash folder (based on some old examples) and we'll see if it survives upgrades. 

sudo mkdir /opt/logstash/vendor/jars
sudo cp /home/gattis/db2jcc4.jar /opt/logstash/vendor/jars
sudo chown -R logstash:logstash /opt/logstash/vendor/jars

Configuration

Configuring the input

Edit the config file like so: sudo vim /etc/logstash/conf.d/logstash.conf

input {
  jdbc {
    jdbc_driver_library => "/opt/logstash/vendor/jars/db2jcc4.jar"
    jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
    jdbc_connection_string => "jdbc:db2://db1.tim.private:50000/itimdb"
    jdbc_user => "itimuser"
    jdbc_password => "somePassword"
    statement => "select * from someTable"
  }
}

Filtering

You don't need to do any pattern matching, as the input emits the event pre-parsed based on the DB columns. You may however, want to match a timestamp in the database like so:

# A sample value in the 'completed' column is 2016-04-07 00:41:03:291 GMT
filter {
  date {
    match => [ "completed" , "yyyy-MM-dd HH:mm:ss:SSS zzz" ]
  }
}


Output

One trick recommended is to  link the primary keys between the database and kibana. That way, if you run the query again you update the existing elasticsearch records rather than create duplicates ones. Simply tell the output plugin to use the existing primary key from the database for the document_id when it sends it to elasticsearch.

# Database key is the column 'id'
output {
  elasticsearch {
    hosts => ["10.17.153.1:9200"]
    index => "logstash-db-%{+YYYY}"
    document_id => "${id}"
    type => "isim-process"
  }
}



https://www.elastic.co/blog/logstash-jdbc-input-plugin
https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html
https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html


Note:

If any of your columns are non-string type, logstash and elasticsearch will happily store them as such. But be warned that kibana will round them to 16 digits due to a limitation of javascript.

https://github.com/elastic/kibana/issues/4356


Comments