Sqlserver logstash configration


(Elsayed Awd Elsayed Ali) #1

I have windows as well as Elasticsearch 5.1.1 , I made these configuration with sqlserver and logstash .

please what is the error of these configurations .

input {
jdbc {
jdbc_driver_library => "D:\Usefull_Jars\sqljdbc4-4.0.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://192.168.8.111:1433;databaseName=;integratedSecurity=false;user=;password=****;"
jdbc_user => "team"
jdbc_password => "team"
statement => "SELECT * FROM [Sourcing].[direct_feed].[WE_ExtractedRawData] "
}
}
output {
elasticsearch {
host => "localhost:9200"
index => "RawData"
document_type => "DirectFeed"
document_id => "%{ID}"
protocol => "http"
}
stdout { codec => rubydebug }
}


(Magnus Bäck) #2

It's completely impossible to help unless you tell us what the problem is. In what way isn't the configuration working? Are you getting an error message?


(Elsayed Awd Elsayed Ali) #3

thank you for your fast replying :slight_smile:

yes I have these errors

> 09:28:35.275 [LogStash::Runner] ERROR logstash.outputs.elasticsearch - Unknown s
> etting 'host' for elasticsearch

> 09:28:35.275 [LogStash::Runner] ERROR logstash.outputs.elasticsearch - Unknown s
> etting 'protocol' for elasticsearch

> 09:28:35.306 [LogStash::Runner] ERROR logstash.agent - fetched an invalid config
>  {:config=>"input {\n  jdbc {\n    jdbc_driver_library => \"D:\\Usefull_Jars\\sq
> ljdbc4-4.0.jar\"\n    jdbc_driver_class => \"com.microsoft.sqlserver.jdbc.SQLSer
> verDriver\"\n    jdbc_connection_string => \"jdbc:sqlserver://192.168.2.107:1433
> ;databaseName=Sourcing;integratedSecurity=false;user=team;password=team;\"\n
> jdbc_user => \"team\"\n    jdbc_password => \"team\"\n    statement => \"SELECT
> * FROM [Sourcing].[direct_feed].[WE_ExtractedRawData] \"\n  }\n}\noutput {\n  el
> asticsearch {\n    host => \"localhost\"\n    index => \"RawData\"\n    document
> _type => \"DirectFeed\"\n    document_id => \"%{ID}\"\n    protocol => \"http\"\
> n  }\n  stdout { codec => rubydebug }\n}\n", :reason=>"Something is wrong with y
> our configuration."}

(Magnus Bäck) #4

The host and protocol options are for Logstash 1.x only. You should use hosts instead. See the documentation of the elasticsearch output for available options and what they mean.


(Elsayed Awd Elsayed Ali) #5

ok I have already made this and it worked fine .


(Elsayed Awd Elsayed Ali) #6

please @magnusbaeck How to track the updates of sqlserver like insert ,update ,delete into Elastic search via above configurations


(Magnus Bäck) #7

The stdout output that you already have will tell you what Logstash is emitting. Note that it won't delete anything from Elasticsearch.


(Christian Dahlqvist) #8

In order to track updates, inserts and deletes you will need to design your query so that it retrieves the desired changes. In order to not continuously retrieve all records you may want to use the sql_last_value and compare this to a field in your data in order to only get changes since the last run.


(system) #9

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