Logstash jdbc schedule missing values

[sadeshdrj (sadesh warnakulasooriya)]

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

1. i have "left join" in statement query.

I would greatly appreciate it if you kindly give me some feedback on this,

[sadeshdrj (sadesh warnakulasooriya)]

Any suggestions, please ??

[ITIC (Jordi)]

Hi

Have you tried your select outside of logstash? Does it work as expected?

I think the behaviour you see might be due to the left join with table users, where the id present on iss is not present in usr.

It's been a while since I wrote a select, but I remember there was a way to define the joins in case some data is missing on the joined table, otherwise the data from iss is ignored and those rows are not returned.

Sorry I cannot be more specific off the top of my head.

Hope this helps.