# Relational inputs of logstash in jdbc plugin **URL:** \ **Category:** Logstash\ **Created:** [February 24, 2019, 1:26pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724 "2019-02-24T13:26:47Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [February 24, 2019, 1:26pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/1 "2019-02-24T13:26:48Z")
hi all, i am using following configuration in logstash: ``` input { jdbc { jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar" jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN" jdbc_user => "sss" jdbc_password => "sss" clean_run => "true" statement => "DECLARE @DDate CHAR(10) select @DDate=MAX(Date) from TotalTXN.dbo.TotalTxn_Control SELECT TotalTXN.dbo.d2md(@DDate) as gdate" } jdbc { jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar" jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN" jdbc_user => "sss" jdbc_password => "sss" clean_run => "true" statement => " select TOP 2 [BaseDate_Accept_Setel] from dbo.vw_Total where (BaseDate_Accept_Setel=(SELECT REPLACE(MAX(Date),'/','') from TotalTXN.dbo.TotalTxn_Control)) AND (BaseDate_Accept_Setel> :sql_last_value) " use_column_value => "true" tracking_column => "BaseDate_Accept_Setel" } } filter { mutate { gsub => [ "gdate", "/", "-" ] } } output { elasticsearch { hosts => ["http://192.168.170.153:9200"] index => "a2_%{gdate}" user => "logstash_internal25" password => "x-pack-test-password" } stdout { codec => rubydebug } } ``` when logstash be started, the data of first jdbc input indexed by value of gdate, but data of second jdbc input indexed as word "a2\_%{gdate}" instead of value of gdate actually, it seems , when data in second jdbc is selecting, it has no information of gdate's value. now, how can i handle this issue? i want that output data of second jdbc input be indexed same as first jdbc input. could you please advise me about this? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [February 24, 2019, 2:03pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/2 "2019-02-24T14:03:39Z")
Each input is run independently. I do not think there is any order-of-execution guarantee. You might be able to add gdate to the events from the second jdbc input with either a jdbc\_static or jdbc\_streaming filter. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [February 24, 2019, 2:31pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/3 "2019-02-24T14:31:29Z")
many thanks, I chanmged my configuration as following: ``` input { jdbc { jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar" jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN" jdbc_user => "sss" jdbc_password => "sss" clean_run => "true" statement => " select TOP 2 [BaseDate_Accept_Setel] from dbo.vw_Total where (BaseDate_Accept_Setel=(SELECT REPLACE(MAX(Date),'/','') from TotalTXN.dbo.TotalTxn_Control)) AND (BaseDate_Accept_Setel> :sql_last_value) " use_column_value => "true" tracking_column => "BaseDate_Accept_Setel" } } filter { jdbc_streaming { jdbc_driver_library => "F:\driver\sqljdbc_6.0\enu\jre8\sqljdbc42.jar" jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "jdbc:sqlserver://local:3333;databaseName=TotalTXN" jdbc_user => "sss" jdbc_password => "sss" statement => "DECLARE @DDate CHAR(10) select @DDate=MAX(Date) from TotalTXN.dbo.TotalTxn_Control SELECT TotalTXN.dbo.d2md(@DDate) as gdate" target => "gdate" } mutate { gsub => [ "gdate", "/", "-" ] } } output { elasticsearch { hosts => ["http://192.168.170.153:9200"] index => "a6_%{gdate}" user => "logstash_internal25" password => "x-pack-test-password" } stdout { codec => rubydebug } } ``` but it give me following error: `[2019-02-24T17:42:55,369][ERROR][logstash.outputs.elasticsearch] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"a6_{gdate=2019/02/23}", :_type=>"doc", :routing=>nil}, #], :response=>{"index"=>{"_index"=>"a6_{gdate=2019/02/23}", "_type"=>"doc", "_id"=>nil, "status"=>400, "error"=>{"type"=>"invalid_index_name_exception", "reason"=>"Invalid index name [a6_{gdate=2019/02/23}], must not contain the following characters [, \", *, \\, <, |, ,, >, /, ?]", "index_uuid"=>"_na_", "index"=>"a6_{gdate=2019/02/23}"}}}}` actually, it seems mutate filter is not working ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [February 24, 2019, 2:54pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/4 "2019-02-24T14:54:41Z")
If a jdbc\_streaming filter is configured with ``` target => "foo" statement => "select ... as bar" ``` then foo is an array of bar. So in your case the data you get back is the equivalent of ``` { "gdate" : [{ "gdate": "2019/02/23" }] } ``` So this should work ``` mutate { replace => { "gdate" => "%{[gdate][0][gdate]}" } } mutate { gsub => ["gdate", "/", "-"] } ``` ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [February 24, 2019, 2:58pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/5 "2019-02-24T14:58:51Z")
it works. many many thanks. can i ask another question? I am using tracking\_column to have a query checkpoint. it is expected to save the last value of "basedate\_accept\_setel" in the sql\_last\_value, but in the .logstash\_jdbc\_last\_run , i can just see the value "--- 0" while it should have value fore example as "20190101". how can i handle this issue? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [February 24, 2019, 3:12pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/6 "2019-02-24T15:12:45Z")
I suggest you start a new thread for that new question. ---
**Author:** ![sahere37](https://avatars.discourse-cdn.com/v4/letter/s/b2d939/32.png) [@sahere37](https://discuss.elastic.co/u/sahere37)\ **Post date:** [February 24, 2019, 3:40pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/7 "2019-02-24T15:40:55Z")
thanks for your reply. actually, i asked before, and waiting for inspiring advice > [@Logstash jdbc plugin sql\_last\_value](https://discuss.elastic.co/t/logstash-jdbc-plugin-sql-last-value/169704): > > hi all, I am using logstash jdbc plugin as following: input { jdbc { jdbc\_driver\_library =\> "F:\driver\sqljdbc\_6.0\enu\jre8\sqljdbc42.jar" jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc\_connection\_string =\> "jdbc:sqlserver://localhost:3333;databaseName=TotalTXN" jdbc\_user =\> "sss" jdbc\_password =\> "sss" schedule =\> "\* \*/1 \* \* \*" clean\_run =\> "true" statement =\> "SELECT date FROM dbo.TotalTxn\_Control" use\_column\_value =\> "true" tracking\_… ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 24, 2019, 3:40pm UTC](https://discuss.elastic.co/t/relational-inputs-of-logstash-in-jdbc-plugin/169724/8 "2019-03-24T15:40:55Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.