Hi There,
following i have running input jdbc method in logstash, i have noticed in my discover index the one table column "user name" data is missing or might appear but one few records or sometimes even the table column doesn't appear in "available fields". i have tried every possible way that i can find from the documentation pages and elastic forum's but still i couldn't figure it out a solution, for the record i don't see any error in log as well.
following is my configuration,
input {
jdbc {
jdbc_connection_string => "jdbc:mysql://SEVER_IP:5601/DB_NAME"
jdbc_user => "user_Name"
jdbc_password => "Password"
jdbc_driver_library =>"/usr/share/logstash/mysql-connector-java.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
use_column_value => true
tracking_column => "id"
record_last_run => true
last_run_metadata_path => "/etc/logstash/jdbc_last_run_login_log.yml"
statement => "select
iss.id id
,iss.created_on created_on
,iss.updated_on updated_on
,iss.status_id status_id
,tra.name tracker_name
,concat(usr.lastname,' ',usr.firstname) as user_name
,tra.id tracker_id
from
issues iss
left join users usr
on iss.assigned_to_id = usr.id
left join trackers tra
on iss.tracker_id = tra.id > :sql_last_value"
schedule => "*/2 * * * *"
} }
output {
elasticsearch {
hosts => ["127.0.0.1:9200"]
user => user
password => password
index => "users-issues-name-assign"
document_type => "_doc"
document_id => "%{id}"
} }
following is my logstash log,
[2019-12-13T13:57:14,984][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output
{:class=>"LogStash::Outputs::ElasticSearch", :hosts=>["//127.0.0.1:9200"]}
[2019-12-13T13:57:15,192][INFO ][logstash.outputs.elasticsearch] Using default mapping template
[2019-12-13T13:57:15,300][INFO ][logstash.outputs.elasticsearch] Using default mapping
templateatch_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>
{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>
{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>
{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"},
"longitude"=>{"type"=>"half_float"}}}}}}}
[2019-12-13T13:57:17,067][INFO ][logstash.outputs.elasticsearch] Attempting to install template
{:manage_template=>{"index_patterns"=>"logstash-*", "version"=>60001, "settings"=>
{"index.refresh_interval"=>"5s", "number_of_shards"=>1}, "mappings"=>
{"dynamic_templates"=>[{"message_field"=>{"path_match"=>"message",
"match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}},
{"string_fields"=>{"match"=>"*", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date"}, "@version"=>{"type"=>"keyword"}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}
[2019-12-13T13:57:17,101][WARN ][org.logstash.instrument.metrics.gauge.LazyDelegatingGauge] A gauge metric of an unknown type (org.jruby.specialized.RubyArrayOneObject) has been create for key: cluster_uuids. This may result in invalid serialization. It is recommended to log an issue to the responsible developer/development team.
[2019-12-13T13:57:17,181][INFO ][logstash.javapipeline ] Starting pipeline {:pipeline_id=>"SB", "pipeline.workers"=>1, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50, "pipeline.max_inflight"=>125, :thread=>"#<Thread:0x77044bd0 run>"}
[2019-12-13T13:57:18,721][INFO ][logstash.javapipeline ] Pipeline started {"pipeline.id"=>"SB"}
[2019-12-13T13:57:21,309][INFO ][logstash.agent ] Pipelines running {:count=>1, :running_pipelines=>[:suibou], :non_running_pipelines=>[]}
[2019-12-13T13:57:26,141][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2019-12-13T13:58:54,988][INFO ][logstash.inputs.jdbc ] (0.109188s) select
iss.id id
,iss.created_on created_on
,iss.updated_on updated_on
,iss.status_id status_id
,tra.name tracker_name
,concat(usr.lastname,' ',usr.firstname) as user_name
,tra.id tracker_id
from
issues iss
left join users usr
on iss.assigned_to_id = usr.id
left join trackers tra
on iss.tracker_id = tra.id > 0
[2019-12-13T13:58:55,358][INFO ][logstash.inputs.jdbc ] (0.546218s)
select
*
from
issues
fro
[2019-12-13T14:00:01,025][INFO ][logstash.inputs.jdbc ] (0.047451s) select
iss.id id
,iss.created_on created_on
,iss.updated_on updated_on
,iss.status_id status_id
,tra.name tracker_name
,concat(usr.lastname,' ',usr.firstname) as user_name
,tra.id tracker_id
from
issues iss
left join users usr
on iss.assigned_to_id = usr.id
left join trackers tra
on iss.tracker_id = tra.id > 266
Note
- i have "left join" in statement query.
I would greatly appreciate it if you kindly give me some feedback on this,