Logstash out put to SQL Server

Elastic Stack Logstash
1

@Badger

Hi I am using log stash to output to csv -- it is working fine.

However I am trying to write from Elasticsearch to sql server using jdbc driver which is giving error.
Can you please try to pick what I am doing. Thank in advance.

I followed the help from here.
https://discuss.elastic.co/t/logstash-data-pipeline-from-elastic-to-sql-server/123589

Input {
 elasticsearch {
    hosts => "localhost:9200"
    index => "idx-discoveryproduct"
	user => "#####"
    password =>"########"
    query => '
    {
	   "query": {
    "bool": {
      "must": [
        {
          "match": {
            "cop": "United States"
          }
        },
		{
          "match": {
            "author": "Source Wikia"
          }
        }
      ]
    }
  }
	  
    } 
  '
  }
}
output {

jdbc{
driver_jar_path => 'C:\Work\ElasticSearch\logstash-8.1.2\vendor\jar\jdbc\sqljdbc_10.2\enu/mssql-jdbc-10.2.0.jre17.jar'
connection_string => “jdbc:sqlserver://xxx.xxx.xx.xxx;databaseName=TempFeed;user=Nxxxxxx;password=xxxxx”
statement => [ “INSERT into dataFeedStage (Isbn13, Audience, Author,SessionKey,COP,timeStamp) VALUES("isbn13","title","author","audiences","12345","cop",“@timestamp”)", “client_num", “consignee_name", “@timestamp","host" ]
}
   
}

Error I am getting is

Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", 
:message=>"Expected one of [ \\t\\r\\n], \"#\", [A-Za-z0-9_-], '\"', \"'\", [A-Za-z_], \"-\", [0-9], \"[\", \"{\" at line 40, column 22 (byte 675) after output {\r\n\r\njdbc{\r\ndriver_jar_path => 'C:\\Work\\ElasticSearch\\logstash-8.1.2\\vendor\\jar\\jdbc\\sqljdbc_10.2\\enu/mssql-jdbc-10.2.0.jre8.jar'\r\nconnection_string => ", :backtrace=>["C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/compiler.rb:32:in `compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:189:in `initialize'", "org/logstash/execution/JavaBasePipelineExt.java:72:in `initialize'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:47:in `initialize'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/pipeline_action/create.rb:50:in `execute'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/agent.rb:376:in `block in converge_state'"]}
[2022-04-14T18:45:14,189][INFO ][logstash.runner          ] Logstash shut down.
[2022-04-14T18:45:14,194][FATAL][org.logstash.Logstash    ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit
        at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:747) ~[jruby.jar:?]
        at org.jruby.RubyKernel.exit(org/jruby/RubyKernel.java:710) ~[jruby.jar:?]
        at C_3a_.Work.ElasticSearch.logstash_minus_8_dot_1_dot_2.lib.bootstrap.environment.<main>(C:\Work\ElasticSearch\logstash-8.1.2\lib\bootstrap\environment.rb:94) ~[?:?]
2

The error is because it appears that you are mixing smart/curly quotes (“ or ”) and straight quotes ("). You should only be using straight quotes in your Logstash configuration(s), specifically for your connection_string which is what the error is pointing out ... and also clean up the statement value as well.

Beyond that, I'm not sure if there will be other issues and assume everything else is correct - the link you included does highlight this is a third party plugin that has not had any updates since 2016.

3

Hi @angelo
Thank you for reply.
It looks like, your suggestions are helping now I am getting below error:

[2022-04-19T10:12:23,344][ERROR][logstash.javapipeline    ][main] Pipeline error {:pipeline_id=>"main", :exception=>#<LogStash::ConfigurationError: JDBC - Could not find jar file at given path. Check config.>, :backtrace=>["C:/Work/ElasticSearch/logstash-8.1.2/vendor/bundle/jruby/2.5.0/gems/logstash-output-jdbc-5.4.0/lib/logstash/outputs/jdbc.rb:179:in `load_jar_files!'", "C:/Work/ElasticSearch/logstash-8.1.2/vendor/bundle/jruby/2.5.0/gems/logstash-output-jdbc-5.4.0/lib/logstash/outputs/jdbc.rb:113:in `register'", "org/logstash/config/ir/compiler/OutputStrategyExt.java:131:in `register'", "org/logstash/config/ir/compiler/AbstractOutputDelegatorExt.java:68:in `register'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:232:in `block in register_plugins'", "org/jruby/RubyArray.java:1821:in `each'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:231:in `register_plugins'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:593:in `maybe_setup_out_plugins'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:244:in `start_workers'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:189:in `run'", "C:/Work/ElasticSearch/logstash-8.1.2/logstash-core/lib/logstash/java_pipeline.rb:141:in `block in start'"], "pipeline.sources"=>["C:/Work/ElasticSearch/logstash-8.1.2/outputJdbc.conf"], :thread=>"#<Thread:0x26173aa1 run>"}
[2022-04-19T10:12:23,346][INFO ][logstash.javapipeline    ][main] Pipeline terminated {"pipeline.id"=>"main"}
[2022-04-19T10:12:23,370][ERROR][logstash.agent           ] Failed to execute action {:id=>:main, :action_type=>LogStash::ConvergeResult::FailedAction, :message=>"Could not execute action: PipelineAction::Create<main>, action_result: false", :backtrace=>nil}
[2022-04-19T10:12:23,417][INFO ][logstash.runner          ] Logstash shut down.
[2022-04-19T10:12:23,424][FATAL][org.logstash.Logstash    ] Logstash stopped processing because of an error: (SystemExit) exit
org.jruby.exceptions.SystemExit: (SystemExit) exit

below is my output config, the jar file available in the location specified.

output {
jdbc{
driver_jar_path => "/vendor/jar/jdbc/sqljdbc_10.2/enu/mssql-jdbc-10.2.0.jre17.jar"
connection_string => "jdbc:sqlserver://XXXXXXXX;databaseName=TempFeed;user=user;password=password"
statement => ["INSERT into my_table (Isbn13, Title,Audience, Author,SessionKey,COP,dtTime) VALUES('isbn13','title','author','audiences','12345','cop','?')","@timestamp"]
}
   
}

Please advise if I am doing anything wrong.

4

I'm not sure of the exact syntax required for Logstash on Windows - if the driver_jar_path is actually on the C: drive in the directory you noted, I would suggest adding that to your config:

driver_jar_path => "C:/vendor/jar/jdbc/sqljdbc_10.2/enu/mssql-jdbc-10.2.0.jre17.jar"

Other thoughts would be to confirm the file location and read permissions, etc.

5

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