Logstash JDBC importer MSSQL2012 is not working

Elastic Stack Logstash
1

Hi,

I'm completely stuck.
I tried to import data from an MSSQL2012 database to elasticsearch, but it's not working.

Here are the steps I've done:

  1. Started Elasticsearch 2.2.0
  2. Downloaded Logstash-all-plugins-2.2.0
  3. Downloaded Elasticsearch-jdbc-2.2.0 from GitHub
  4. Created a .conf file like this:

input {
jdbc {
jdbc_driver_library => "D:\temp\sqljdbc\sqljdbc_4.2\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://DBSVR_NAME;user=;password=;"
jdbc_user => ""
jdbc_password => ""
statement => "SELECT *
FROM [DB].[SCHEMA].[TABLE]"
}
}
filter {
}
output {
elasticsearch {
host => "localhost"
index => "INDEX_NAME"
document_type => "DOCUMENT_TYPE"
document_id => "%{id}"
protocol => "http"
}
stdout { codec => rubydebug }
}

  1. Started Logstash with the command "logstash -f db.conf"
  2. Got an error message that said: "Java::net.sourceforge.jtds.jdbc.Driver not loaded, try install jdbc-jtds gem"
  3. Downloaded the latest jtds .jar and now I don't know what to do.

I've tried other drivers an they didn't work either.

I don't know if this is necessary but the system is a Windows 7 Professional x64.

I'm thankful for any help or alternatives that help me to get my sql data to elasticsearch.

2

Your config does not look correct to me. I have this working against SQL 2012 with the following configuration:

 jdbc {
            jdbc_driver_library => "/PATH/sqljdbc4.jar"
            jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            jdbc_connection_string => "jdbc:sqlserver://SERVERNAME:PORT;databaseName=DBNAME"
            jdbc_user => "USER"
            jdbc_password => "PASSWORD"
            statement => "SELECT BLAH'
}

If you are running a named instance on the SQL Server, then it will likely have a dynamic port allocated, so this number will need to be confirmed from your DBA

3

Thats right.. it seems the issue is with the jdbc connection string.. I use the same 2012 setup with the syntax:
jdbc_connection_string => "jdbc:sqlserver://SQLServername\instance:port;"

Hope this helps.
Vikram Y

4

Thanks, I'll try it and tell you if it's working!