# Logstash jdbc schedule missing values

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748>\
**Category:** Logstash\
**Tags:** elastic-stack-monitoring\
**Created:** [December 13, 2019, 5:27am UTC](https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748 "2019-12-13T05:27:09Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sadeshdrj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sadeshdrj/32/59324_2.png) [@sadeshdrj](https://discuss.elastic.co/u/sadeshdrj)\
**Post date:** [December 13, 2019, 5:27am UTC](https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748/1 "2019-12-13T05:27:09Z")

</div>

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,

---

<div class="post-metadata">

**Author:** ![sadeshdrj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sadeshdrj/32/59324_2.png) [@sadeshdrj](https://discuss.elastic.co/u/sadeshdrj)\
**Post date:** [December 20, 2019, 1:25am UTC](https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748/2 "2019-12-20T01:25:35Z")

</div>

Any suggestions, please ?? 😥

---

<div class="post-metadata">

**Author:** ![ITIC](https://avatars.discourse-cdn.com/v4/letter/i/90ced4/32.png) [@ITIC](https://discuss.elastic.co/u/ITIC)\
**Post date:** [December 20, 2019, 7:24am UTC](https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748/3 "2019-12-20T07:24:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 17, 2020, 7:24am UTC](https://discuss.elastic.co/t/logstash-jdbc-schedule-missing-values/211748/4 "2020-01-17T07:24:10Z")

</div>

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