Multiple jdbc SQL Statements ( & schedules, ideally)

seth.yes · September 6, 2016, 11:18pm

I'm using the most up to date version of Logstash (1:2.2.4-1) and the associated jdbc logstash plugin. My one SQL statement is configured and operating on schedule as expected, querying a MS SQL instance every minute. However, I'm wanting to run multiple SQL statements at the same time, as my business need is to pull data from ~ten different tables. Furthermore, much of this data is more-static and thus doesn't have to be queried as often. It appears I can link my SQL queries back-to-back and not receive a warning, but the second statement never executes.. I've also tried multiple 'statement' blocks inside the jdbc filter, but then no data gets pulled from the MS SQL instance. I'd prefer a more-elegant way to query all the data I need rather than placing it in a view or doing some weird unions.

My questions;

Is there a way to run multiple SQL queries inside the jdbc plugin? What's the workaround if I can only have one SQL statement per jdbc input class?
1a. Is there a way to add different schedules for different statements?
Where is the '.sql_last_value' kept in Unix? I found it about a week ago and haven't came across it since.

Apologies if the answers to my questions are common knowledge -- I looked all over the net before asking the community.

warkolm · September 7, 2016, 2:22am

Just define separate jdbc inputs, that should keep it clean and simple.

seth.yes · September 7, 2016, 4:04pm

Thanks for your reply, I got it to work by placing two consecutive jdbc classes in the same input block, as seen below. The issue was with one of my SQL statements, although I'm unsure what the issue with the SQL was, as it executes fine from SSMS.

 
input {
  jdbc {
    jdbc_driver_library => "/usr/local/sbin/sqljdbc_6.0/enu/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://msSQLServer:1433;user=SQLUser;password=SQLUser!;"
    jdbc_user => "SQLUser"
    jdbc_password => "SQLUser!"
    statement => "SELECT [Time_Stamp]
      ,other-fields
  FROM [SQLDB].[dbo].[SQLTable]"
    schedule => "* * * * *"
    type => "CustomType"
    add_field => { "SQLDescription" => "SQLDesc1" }
    sql_log_level => "debug"
  }
  jdbc {
    jdbc_driver_library => "/usr/local/sbin/sqljdbc_6.0/enu/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://msSQLServer:1433;user=SQLUser;password=SQLUser!;"
    jdbc_user => "SQLUser"
    jdbc_password => "SQLUser!"
    statement => "SELECT [Time_Stamp]
      ,other-fields
  FROM [SQLDB].[dbo].[SQLTable2]"
    schedule => "* * * * *"
    type => "CustomType"
    add_field => { "SQLDescription" => "SQLDesc2" }
    schedule => "*\10 * * * *"
  }
}

Additionally, thanks to @jcome, I found that the input-jdbc configuration specifies the last run time;

"# Path to file with last run time
config :last_run_metadata_path, :validate => :string, :default => "#{ENV['HOME']}/.logstash_jdbc_last_run"