Logstash cannot call SQL Stored Procedure

Elastic Stack Logstash
1

Hi ELK Folks,
I have an issue with pulling the data by calling a SQL Stored Procedure. I am using logstash agent to push the data and we are trying to pull in SQL Data in the form of a Stored Procedure.
Its keep on throwing an error saying that the syntax is wrong...
I checked the documentation to find what is the correct syntax to use in the .conf file to call data from a stored procedure..
Any inputs here folks..

Thanks
Vikram Y

2

If you show us what you've tried (i.e. your configuration) and the exact error message it might be possible to help you.

3

Hi Magnus Bäck,
We got the solution here. Instead of the statement (where we add the sql stament) we called a file with the stored proc with value 'statement_filepath" and it worked fine.
Thanks
Vikram Y

4

Hi Vikram,
I am a newbie to ElasticSearch, can you please provide he steps you took in order to achieve this.

Thanks & Regards,
vamsi

5

Hi Vamsidhar,
If u use the full sql statement then use this in ur logstash config file:
statement => "SELECT Distinct([StartInterv****
If u use a Stored Proc, then save a txt file with the stored proc name and use this in ur logstash config file:
statement_filepath => "C:\Users\filename.txt"

Thanks
Vikram Y

6

Hi Vikram,

Thanks for the quick response, i tried the below method but am still getting the error, please check the config file and the text file below and let me know what i am doing wrong here.

config:
input {
jdbc {
jdbc_driver_library => "C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_6.0\enu\sqljdbc4.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://192.168.91.34\devohms;databaseName=YOLOMED"
jdbc_user => "vamsi"
jdbc_password => "vamsi"
file{
path => "C:\ElasticSearch\logstash-2.3.4\bin\File_Name.txt"
}
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
}
}

IF you want to add Filter you can add one

filter {

.....

#}

output {
elasticsearch {
hosts => "localhost:9200"
index => "indexname"
document_id => "%{column_name}"
document_type => "searchresults"
manage_template => true
}
stdout { codec => rubydebug }
}

File:

DECLARE @RC int

EXECUTE @RC = [dbo].[Stored_Procedure_Name]

Thanks & Regards,
Vamsi

7

Try "statement_filepath " instead of file{
path => "C:\ElasticSearch\logstash-2.3.4\bin\File_Name.txt"
}
Thanks
Vikram Y

8

I have tried the method and it is trowing jdbc sql server exception "incorrect syntax near execute", i have attached the image below.

i have changed the file to have just the below code

execute dbo.procedurename

Pasted image1506×26 3.18 KB

9

It seems ur syntax had some issues. U r using wrong parameters dude. Use mine instead, it will work:

input {
jdbc {
jdbc_driver_library => "C:\Program Files\sqljdbc_6.0\enu\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://Instancename\DBName:1433;"
jdbc_user => "username"
jdbc_password => "password"
statement_filepath => "C:\Users\filepath\sql.txt"
}
}

filter {
mutate {
}

}

output {
stdout { codec => "rubydebug" }
}

Thanks
Vikram Y

10

Hi Vikram,

Thank you so much it worked like a charm.

Thanks & Regards,
Vamsi

11

Cheers buddy.. :slight_smile:

12

I am using Logstash 5.3 and MS SQLServer 2008 R2

Not sure how it worked for you guys... It seems that plugin runs the given SQL as subquery to figure out the column names for prepping the output JSON...

Even placing the SQL in the file and using statement_filepath => "c:\mysql.sql" didn't work... :frowning:

All the help is much appreciated... Thanks

config

input {
  jdbc {

   jdbc_driver_library => "C:/sqljdbc/sqljdbc_6.0/enu/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://oursqlserver:12230;databaseName=WorkArea"
    jdbc_user => "dbuser"
    jdbc_password => "****"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
    statement => [ "exec dbo.stp_get_delta_info 999"]
   }
}

Error

17:22:25.729 [[main]<jdbc] INFO logstash.inputs.jdbc - (0.173000s) SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)
17:22:25.810 [[main]<jdbc] ERROR logstash.inputs.jdbc - Java::ComMicrosoftSqlserverJdbc::SQLServerException:Incorrect syntax near the keyword 'exec'.: SELECT TOP (1) count(*)
AS [COUNT] FROM (exec dbo.stp_get_delta_info 999) AS [T1]
17:22:25.813 [[main]<jdbc] WARN logstash.inputs.jdbc - Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerExc
eption: Incorrect syntax near the keyword 'exec'.>}
17:22:28.567 [LogStash::Runner] WARN logstash.agent - stopping pipeline {:id=>"main"}

13

@Ashish_Viradia

I found this to be a bug, if you run logstash in debug mode the jdbc plugin will do a SELECT TOP(1) on the stored procedure which is invalid.

However, if logstash is not in debug mode then it works fine. Not the best solution but at least a work around.

E

14

I can't run logstash with SQL Stored Procedure. I put the sql statemente in a separate file but I receive the same error always

How I can disable the debug mode?. --log.level=?

I used this command line "./logstash -f importsql.conf --log.level=error" in my osx installation. But always I receive the error SQLServerException due it tries to do [Select top (1) count(*) as [Count] from (exec .......) as [T1].

Please can anyone help me?

Here a list of possible values for logging...

--log.level LEVEL
Set the log level for Logstash. Possible values are:

fatal: log very severe error messages that will usually be followed by the application aborting
error: log errors
warn: log warnings
info: log verbose info (this is the default)
debug: log debugging info (for developers)
trace: log finer-grained messages beyond debugging info