Sqlserver logstash configration

Elastic Stack Logstash
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 }
}

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?

1 Like
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."}
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.

1 Like
5

ok I have already made this and it worked fine .

6

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

7

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

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.

1 Like
9

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