# Logstash JDBC query using joins **URL:** \ **Category:** Logstash\ **Created:** [April 10, 2019, 5:17am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136 "2019-04-10T05:17:06Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 10, 2019, 5:17am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/1 "2019-04-10T05:17:06Z")
Hello @dadoonet, I have the ELK stack installed and all three are communicating correctly. I'm trying to get logstash to query a 'mysql' database. My query contains joins from multiple tables. Can we used joins using “statement” or “statement\_filepath”? If I have written my sql join query in one of the .sql file and provides that filepath to “statement\_filepath”, will this work? Thanks & Regards, Priyanka Yerunkar. ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [April 10, 2019, 6:53am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/2 "2019-04-10T06:53:19Z")
Hello, I've done this for Oracle JDBC connection. I have my query stored in the .sql file with joins and :sql\_last\_value used within. Everything works well. ---
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 10, 2019, 6:59am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/3 "2019-04-10T06:59:23Z")
Hello @saif3r , Thanks for replying. So we have to mention timestamp value as “:sql\_last\_value “ in .sql file. Could you please provide sample example? Thanks & Regards, Priyanka Yerunkar ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [April 10, 2019, 10:24am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/4 "2019-04-10T10:24:31Z")
Sure, there you go. ``` select b.transaction_date as transaction_timestamp, a.*, b.* from dict a left join transactions b on a.name = b.name where b.transaction_date > :sql_last_value ``` Configuration parts required for it: ``` statement_filepath => "/queries/oracle_query.sql" use_column_value => true tracking_column_type => "timestamp" tracking_column => "transaction_timestamp" last_run_metadata_path => "/usr/share/logstash/last_run_metadata/.oracle_query" ``` ---
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 10, 2019, 11:00am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/5 "2019-04-10T11:00:51Z")
Hi @saif3r Thanks for your update!!!! one question regarding :last\_run\_metadata\_path. which path we have to mention here? Thanks, Priyanka Yerunkar ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [April 10, 2019, 11:06am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/6 "2019-04-10T11:06:25Z")
This is the place where logstash stores timestamp/numeric value, used in next run of the pipeline as a reference in place of **:sql\_last\_value**. So in your case file **.oracle\_query** will store transaction\_timestamp from last row of it's previous run. More info can be found here: [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#\_state](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) ---
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 10, 2019, 11:40am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/7 "2019-04-10T11:40:20Z")
Hi @saif3r, Thanks for your quick reply!!! I have passed value for “:last\_run\_metadata\_path”, but it is giving me error as No such file or directory. So we have to create “.oracle\_query” file manually? Could you please guide more on this? Thanks, Priyanka ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [April 10, 2019, 11:43am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/8 "2019-04-10T11:43:06Z")
I think the path ( **/usr/share/logstash/last\_run\_metadata/** ) needs to exist, but the file itself ( **.oracle\_query** ) is created automatically upon first pipieline run. Make sure that **/last\_run\_metadata/** is owned by logstash user. ---
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 10, 2019, 12:09pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/9 "2019-04-10T12:09:33Z")
Hi @saif3r, Thanks for your help!!!!! So file path ( /usr/share/logstash/last\_run\_metadata/) was not there. I have now manually created that and it is not giving me any error. But now I am getting an error while executing sql query. Kindly look at below query I have created and passed “ :sql\_last\_value” as mentioned. So for “:sql\_last\_value”, we have to specify any number or pass the same value? select b.\*, a.External\_link\_Name as ext\_link from externallinks a, externallinkdetails b where b.externalsysid= a.externalsystemid and a.status = 1 and b.status =1 and rownum \< 99999999999999999999999999 \> :sql\_last\_value Thanks, Priyanka ---
**Author:** ![saif3r](https://avatars.discourse-cdn.com/v4/letter/s/49beb7/32.png) [@saif3r](https://discuss.elastic.co/u/saif3r)\ **Post date:** [April 10, 2019, 1:25pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/10 "2019-04-10T13:25:09Z")
This part is not valid: > and rownum \< 99999999999999999999999999 \> :sql\_last\_value Not only it has a wrong syntax, as you are using two comparison operators (\< and \>) within one but also does not make much sense. Rownum depends on current data set, and there's no point using it for :sql\_last\_value. But if you want to give it a shot anyways, alter the last line to: > and rownum \> :sql\_last\_value Initial query should result with rownum \> null. ---
**Author:** ![pyerunka](https://avatars.discourse-cdn.com/v4/letter/p/d6d6ee/32.png) [@pyerunka](https://discuss.elastic.co/u/pyerunka)\ **Post date:** [April 11, 2019, 5:14am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/11 "2019-04-11T05:14:19Z")
Hello @saif3r, Thanks for your great help!!! Now I am able to fetch data from .sql query. Now I am able to fetch data from one query. But I want to fetch it from another 6 - 7 queries and combine data in one single output. Would be possible using logstash? Could you please help me with the same? Thanks & Regards, Priyanka Yerunkar ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 9, 2019, 5:14am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-using-joins/176136/12 "2019-05-09T05:14:21Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.