How to use _sql (SQL queries) to access Elasticsearch indexes in Logstash input plugin

Elastic Stack Logstash
1

Hi
I want to use _sql (SQL queries) to access Elasticsearch indexes in Logstash input plugin. Find my logstash config as below:

input {
http_poller {
urls => {
test1 => {
method => "POST"
user => "abc"
password => "xyz"
url => "http://..**.123:9200/_sql?format=txt"
body => '{
"query": "SELECT id, name FROM testindex"
}'
}
}
request_timeout => 60
codec => "json"
metadata_target => "http_poller_metadata"
}
}

output {
#stdout {}

elasticsearch {
hosts => ["http://..**.123:9200"]
user => "abc"
password => "xyz"
index => "testDest"
}
}

This is not working when starting logstash and giving below error:
message=>"Unable to configure plugins: (ConfigurationError) Something is wrong with your configuration.", :backtrace=>["org.logstash.config.ir.CompiledPipeline.

2

There should be a more specific error from the plugin that says what is wrong with the configuration.

3

Update Config and Error:

input {
http_poller {
urls => {
test1 => {
method => "POST"
user => "abc"
password => "xyz"
url => "http://..**.123:9200/_sql?format=txt"
body => '{"query": "SELECT id, name FROM testindex"}'
headers => {Accept => "application/json"}
}
}
request_timeout => 60
schedule => { cron => "* * * * * UTC"}
codec => "json"
metadata_target => "http_poller_metadata"
}
}

output {
#stdout {}

elasticsearch {
hosts => ["http://..**.123:9200"]
user => "abc"
password => "xyz"
index => "testDest"
}
}

Error:--->

[2021-02-25T10:45:57,893][INFO ][logstash.javapipeline ][main] Pipeline started {"pipeline.id"=>"main"}
[2021-02-25T10:45:57,973][INFO ][logstash.agent ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>}
[2021-02-25T10:45:58,479][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
C:/logstash/vendor/bundle/jruby/2.5.0/gems/rufus-scheduler-3.0.9/lib/rufus/scheduler/cronline.rb:77: warning: constant ::Fixnum is deprecated
{
"error" => "Content-Type header [text/plain; charset=ISO-8859-1] is not supported",
"status" => 406,
"@version" => "1",
"@timestamp" => 2021-02-25T05:16:00.481Z,
"http_poller_metadata" => {
"host" => "IN-ABCD",
"request" => {
"auth" => {
"user" => "abc",
"eager" => true,
"pass" => "xyz"
},
"method" => "post",
"url" => "http://...:9200/_sql?format=txt",
"body" => "{"query": "SELECT * FROM rpalogpoc"}",
"headers" => {
"Accept" => "application/json"
}
},
"response_message" => "Not Acceptable",
"response_headers" => {
"content-type" => "application/json; charset=UTF-8"
},
"name" => "test1",
"runtime_seconds" => 0.204,
"times_retried" => 0,
"code" => 406
}
}

4

You should set a content-type header on the request.

5

@Badger , if you see the update config, its already set in request:
headers => {Accept => "application/json"}

6

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