MS-SQL config with Logstash

  1. Only way to connect MS-SQL (Any database) to a Logstash is thru a JDBC Driver Only (Document is valid only for Logstash 2.2.2).
  2. In this blog i am going to explain how to download from what and how to configure JDBC driver to Logstash.

Pre-requirements :

  1. ElasticSearch
  2. Logstash

Best PractisePlease read documentation before you do anything.

Lets dig-in:

  1. Download JDBC connector from Microsoft JDBC Drivers 6.0 (Preview), 4.2, 4.1, and 4.0 for SQL Server (If link is wrong, You can always google it.)
  2. scroll to the list and download correct file based on your system requirements. for this article sake i am downloading “sqljdbc_6.0.6629.101_enu.exe”
  3. Run exe file and safe it in your project location, see below for my example.
c:\ProjectKibana\MySqlDriver

     4.  Next step, So to your logstash folder

c:\ProjectKibana\Logstash\Logstash 2.2.2\bin

     5. Create a new conf file name JDBCConnector.Conf and Edit as below

input {
  jdbc {
   jdbc_driver_library => "C:\ProjectKibana\MySqlDriver\sqljdbc_6.0\enu\sqljdbc42.jar"
   jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
   jdbc_connection_string => "jdbc:sqlserver://YOUR SERVER NAME:1433;databaseName=YOUR DATABASE NAME"
   jdbc_user => "YOUR DATABASE USERNAME, IF YOU DONT HAVE ONE REMOVE jdbc_user"
   jdbc_password => "YOUR DATABASE PASSWORD, IF YOU DONT HAVE ONE REMOVE jdbc_password"
   statement => " select * from TABLE "
   jdbc_paging_enabled => "true"
   jdbc_page_size => "50000"
   }
 }
# IF you want to add Filter you can add one
# filter {
# .....
#}

output {
 elasticsearch {
 hosts => "localhost:9200"
 index => "Your ES IndexName"
 document_id => "%{Table_id}"
 document_type => "Any Name for your table in ES"
 manage_template => true
 }
 stdout { codec => rubydebug }
}

All Done…. You are good to go…. Now run your conf file  using command prompt.

c:\ProjectKibana\logstash\logstash 2.2.2\bin> logstash -f  JDBCConnector.Conf --debug

Useful information about JDBC :

If you want more information about JDBC itself Please read Building the Connection URL

 

Advertisements

One thought on “MS-SQL config with Logstash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s