Run SQL statement

[Sumit_Gupta1 (Sumit Gupta)]

Hi,
I want to run following SQL statment:

input {
jdbc {
jdbc_driver_library => "/app/logstash-5.6.2/lib/ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521/apextst"
jdbc_user => ""
jdbc_password => ""
jdbc_fetch_size => 3000
statement => "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p"
}
}
output {
stdout { codec => rubydebug }
elasticsearch {
hosts => ["localhost:9200"]
index => "logstash-database-info"
document_type => "mytype"
}
}

This is giving following error:

Sending Logstash's logs to /app/logstash-5.6.2/logs which is now configured via log4j2.properties
[2017-10-29T01:09:56,411][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"netflow", :directory=>"/app/logstash-5.6.2/modules/netflow/configuration"}
[2017-10-29T01:09:56,445][INFO ][logstash.modules.scaffold] Initializing module {:module_name=>"fb_apache", :directory=>"/app/logstash-5.6.2/modules/fb_apache/configuration"}
[2017-10-29T01:09:56,814][ERROR][logstash.agent ] Cannot create pipeline {:reason=>"Expected one of #, {, } at line 9, column 83 (byte 419) after input {\n jdbc {\n jdbc_driver_library => "/app/logstash-5.6.2/lib/ojdbc7.jar"\n jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"\n jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521/apextst"\n jdbc_user => "DBA_WORK"\n jdbc_password => "DBA_WORK123"\n jdbc_fetch_size => 3000\n statement => "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' ""}

I can able to small SQL statement but facing problem to execute big SQL statement.

Can you please help this?

How can i execute big SQL statment?

[Sumit_Gupta1 (Sumit Gupta)]

I'm using following statment:

statement_filepath => "/app/logstash-5.6.2/bin/data.sql AND timestamp >= :sql_last_value"

This is giving following error:

input {
jdbc {
# This setting must be a path
# File does not exist or cannot be opened /app/logstash-5.6.2/bin/data.sql AND timestamp >= :sql_last_value
statement_filepath => "/app/logstash-5.6.2/bin/data.sql AND timestamp >= :sql_last_value"
...
}
}

[magnusbaeck (Magnus Bäck)]

statement => "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p"

This is a double quoted string so you can't have double quotes inside the string. In many cases you'd be able to escape the double quotes inside the string but I'm not sure that works here. Do you really have to mix single a double quotes inside the SQL statement?

[magnusbaeck (Magnus Bäck)]

statement_filepath => "/app/logstash-5.6.2/bin/data.sql AND timestamp >= :sql_last_value"

The "AND timestamp >= :sql_last_value" part must, of course, go inside data.sql.