Hi. How are you?
We are trying to execute the filter "Jdbc_static" to enrich the events with static information present in a database.
The field that we want to use is an arrays strings and we need to perform a query with "IN" operator so that it brings all the names of the list.
The problem is that logstash throws a warning indicating that this field can not be found in the local_lookups, therefore, this field is not created.
This is an example of the input event. The field with the problem is "RequestedHotelIds".
{
"HasError": true,
"User": "127.0.0.1",
"EventDate": "2019-02-02T02:27:08-03:00",
"RequestedHotelIds": [
"90000250",
"90000352",
"90004521"
],
"ExecutionTime": 209,
"RequestedCitiesIds": null,
"ErrorDescription": "asdasd"
}
This is the Logstash script configuration:
input {
kafka {
bootstrap_servers => "10.75.85.204:9092"
topics => "test2101"
codec => "json"
}
}
filter {
mutate {
split => { "RequestedHotelIds" => ","}
}
jdbc_static {
loaders => [
{
id=> "remote-hotels"
query => "SELECT hc.Code as Code, c.Nombre as Nombre from Clientes c inner join HotelCodes hc on hc.id_cliente = c.id where c.tipocliente = 'H'"
local_table => "hotels"
}
]
local_db_objects => [
{
name => "hotels"
index_columns => ["Code"]
columns => [
["Code", "varchar(10)"],
["Nombre", "varchar(100)"]
]
}
]
local_lookups => [
{
query => "select Nombre from hotels where Code in :idparamhotels"
parameters => {idparamhotels => "[RequestedHotelIds]"}
target => "hotelsnames"
}
]
staging_directory => "/tmp/logstash/jdbc_static/import_data"
loader_schedule => "*/30 * * * *"
jdbc_user => "testDB"
jdbc_password => "pws1234"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_driver_library => "/usr/share/logstash/pathDB/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
jdbc_connection_string => "jdbc:sqlserver://55.55.55.55;database=testDB;user=usertest;password=pws1234"
}
}
output {
elasticsearch {
hosts => ["15.41.965.204:9200"]
codec => "json"
index => "logstash-withnames"
}
}
the warning it throws is
[WARN ] 2019-02-21 12:39:10.031 [[main]>worker0] lookup - Parameter field not found in event {:lookup_id=>"lookup-1", :invalid_parameters=>["[RequestedHotelIds]"]}