JDBC Input Multiple sql statements as sub-query

[myersman (Scott Myers)]

I want to know if the jdbc input plugin allows me to query multiple tables where the second table runs only for a key value from the first. I presume the config would look something like...

input {
  jdbc {
    jdbc_driver_library => "c:\path\to\logstash\logstash-core\lib\jars\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=databaseName"
    jdbc_user => "username"
    jdbc_password => "password"
    statement => "select * from table1"
	type => "table1"
  }
  jdbc {
    jdbc_driver_library => "c:\path\to\logstash\logstash-core\lib\jars\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=databaseName"
    jdbc_user => "username"
    jdbc_password => "password"
    statement => "select * from table2 where parentid=" <<<table1.id>>>
	type => "table2"
  }
}
output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "firstindex"
    #user => "elastic"
    #password => "changeme"
  }
}

Is this possible? If not, is there a good alternative?

[elasticforme (Sachin)]

I don't think this is valid logic

basically when you run two input-jdbc two different record comes to logstash.
there is no way second jdbc will know field value from one input.

I think you can do filter called "jdbc_streaming" which can run another jdbc in filter section.
You have to test it to see if it works or not.

[Rahul_Kumar4 (Rahul Kumar)]

@myersman If your purpose here is to output rows only from table2 to your elasticsearch output then you can try filtering them in a single jdbc input block instead of using two.

 jdbc {
    jdbc_driver_library => "c:\path\to\logstash\logstash-core\lib\jars\sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=databaseName"
    jdbc_user => "username"
    jdbc_password => "password"
    statement => "SELECT * from table2 WHERE table2.parentid IN (SELECT table1.id FROM table1)"
  }