JDBC_Static filter is not mapping the data from postgresql

I have loded lookup table in postgresql and used jdbc_static filter to enrich the data. while executing i am getting the below error. In staging directory the data is not loaded.
Can some one please help what is the issue.

Exception when executing Jdbc query {:lookup_id=>"local-servers", :exception=>"Java::JavaSql::SQLSyntaxErrorException: Syntax error: Encountered ":" at line 1, column 57.", :backtrace=>["org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(org/apache/derby/impl/jdbc/SQLExceptionFactory)", "org.apache.derby.impl.jdbc.Util.generateCsSQLException(org/apache/derby/impl/jdbc/Util)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(org/apache/derby/impl/jdbc/TransactionResourceImpl)", "org.apache.derby.impl.jdbc.EmbedConnection.handleException(org/apache/derby/impl/jdbc/EmbedConnection)", "org.apache.derby.impl.jdbc.ConnectionChild.handleException(org/apache/derby/impl/jdbc/ConnectionChild)", "org.apache.derby.impl.jdbc.EmbedStatement.execute(org/apache/derby/impl/jdbc/EmbedStatement)", "org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(org/apache/derby/impl/jdbc/EmbedStatement)"]}

Mu logstash config file is

input {
file {
path => "C:/Users/605824803/Documents/data/invcs.csv"
start_position => "beginning"
sincedb_path => "C:/Users/605824803/Documents/data/null26"

}

}
filter {
csv{
separator => ","
columns => ["host_ip","destin"]
skip_empty_columns => false
}
jdbc_static {
loaders => [
{
id => "remote-users"
query => "select host_ip, host_name from host_mapping"
local_table => "users"
}
]
local_db_objects => [
{
name => "users"
index_columns => ["host_ip"]
columns => [
["host_ip", "varchar(50)"],
["host_name", "varchar(50)"]
]
}
]
local_lookups => [
{
id => "local-servers"
query => "SELECT host_name as HostName FROM users WHERE host_ip = :host_ip"
target => "user"
}
]
staging_directory => "C:/Users/605824803/Documents/data/postgresdata"
jdbc_user => "postgres"
jdbc_password => "anji"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_driver_library => "C:/Users/605824803/Downloads/postgresql-42.2.12.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/Monitoring"
}
}
output {
elasticsearch {
hosts => "http://localhost:9200"
manage_template => false
index => "test-host6"

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

You need to define the host_ip parameter, otherwise it does not get substituted and the jdbc driver chokes on it.

Hi Badger,

Thanks for the help. it is working now.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.