Concat query is not working in logstash

Elastic Stack Logstash
1

Hi,

I am trying to combine 2 fields first name & last name and trying to populate as single field but getting some error. Below is my Logstash config file emp-index-logstash.conf:-

input {

jdbc {

    jdbc_connection_string => "jdbc:oracle:thin:@//10.65.40.144:1521/ORCL"

    jdbc_user => "deepshara"

    jdbc_password => "deepshar"

    jdbc_validate_connection => true

    jdbc_driver_library => "/data/isuser/ojdbc6.jar"

    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"

    statement => 'SELECT employee_id, concat(concat(first_name,' '), last_name) "employee_name", email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id FROM deepshara.employees ORDER BY hire_date ASC'


}

}

output {

elasticsearch {

    index => "employee"

    document_type => "emp_data"

    hosts => [ "10.75.155.35:18002" ]

}
    stdout{

            codec=>rubydebug

    }

can anyone help me where i am going wrong.

Thanks,
Deepak

2

What error are you getting?

3

Below is the content from logfile logstash-plain.log:-

Sending Logstash's logs to /data/elk/logstash-6.2.4/logs which is now configured via log4j2.properties
[2018-06-08T09:19:06,066][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"/data/elk/logstash-6.2.4/modules/fb_apache/configuration"}
[2018-06-08T09:19:06,099][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"/data/elk/logstash-6.2.4/modules/netflow/configuration"}
[2018-06-08T09:19:06,696][WARN ][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified
[2018-06-08T09:19:07,590][INFO ][logstash.runner ] Starting Logstash {"logstash.version"=>"6.2.4"}
[2018-06-08T09:19:08,073][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>xyx}
[2018-06-08T09:19:08,827][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, => at line 20, column 5 (byte 589) after input {\n\n jdbc {\n\n jdbc_connection_string => "jdbc:oracle:thin:@//local:1521/ORCL"\n\n jdbc_user => "deepshara"\n\n jdbc_password => "deepshar"\n\n jdbc_validate_connection => true\n\n jdbc_driver_library => "/data/elk/ojdbc6.jar"\n\n jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"\n\n statement => 'SELECT employee_id, concat(concat(first_name,' '), last_name) "employee_name", email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id FROM schema_name.employees ORDER BY hire_date ASC'\n\n\n ", :backtrace=>["/data/elk/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:42:in compile_imperative'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:50:incompile_graph'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:12:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/compiler.rb:11:in compile_sources'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/pipeline.rb:51:ininitialize'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/pipeline.rb:169:in initialize'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/pipeline_action/create.rb:40:inexecute'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:315:in block in converge_state'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:141:inwith_pipelines'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:312:in block in converge_state'", "org/jruby/RubyArray.java:1734:ineach'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:299:in converge_state'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:166:inblock in converge_state_and_update'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:141:in with_pipelines'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:164:inconverge_state_and_update'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/agent.rb:90:in execute'", "/data/elk/logstash-6.2.4/logstash-core/lib/logstash/runner.rb:348:inblock in execute'", "/data/elk/logstash-6.2.4/vendor/bundle/jruby/2.3.0/gems/stud-0.0.23/lib/stud/task.rb:24:in `block in initialize'"]}

4

I suggest you to use double quotes instead of single quotes for sql statement and use as keyword to define new column. My test config is below.

statement => "select description, concat(concat(username,' '),description) as new_col from ne_api_user"

5

Yeah, the problem is using (first_name, ' ') with single quotes inside a statement that is delimited by single quotes.

6

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