Jdbc_streaming filter help

[ripopo (alexis)]

Hello, for example i have a database with columns id, a, b, c.
here is my config file
<
input {
stdin { }
}

filter {
jdbc_streaming {
jdbc_driver_library => "path/to/data.jar"
jdbc_driver_class => "..."
jdbc_connection_string => "..."
jdbc_user => "user"
jdbc_password => "passwd"
parameters => { "p" => "param"}
statement => "SELECT id, identifiant, division, businessUnit, codePlateforme, projectManagerNom, client, createur from int_central_gestionnairecipdb.dbo.constellation WHERE b = :p"
target => "data"
}
}

output {
stdout {

}

}
/>
here is the output in stdout
<
{
"@timestamp" => 2018-08-01T07:41:56.343Z,
"data" => [
[0] {}
],
"message" => "test\r",
"@version" => "1",
"host" => "host",
"tags" => [
[0] "_jdbcstreamingdefaultsused"
]
}
/>
it seems that it doesnt find the value param in the column b but i'm sure it is.
Do you know why ?

[guyboertje]

@ripopo

statement => "SELECT id, identifiant, division, businessUnit, codePlateforme, projectManagerNom, client, createur from int_central_gestionnairecipdb.dbo.constellation WHERE b = :p"
parameters => { "p" => "param"}

The above is saying: "Get the param field's value from the the event and replace the token :p with it in the statement.
But you don't have a field called param in the event at the time it is processed by the jdbc_streaming filter.
You have a message field, but its value has a \r carriage return at the end.

Here is a working example using my test postgres db:
Notes

1. I use the generator input all the time to send test data into a pipeline, in this case its a fixed size delimited string (3 of them) in the lines array setting - each line becomes a Logstash Event.
2. I used the dissect filter to break up the fixed size delimited string into values for fields in the event.
3. I converted the amount and loggedin_userid fields to their numeric equivalents.

input {
  generator {
    lines => [
      '10.2.3.40;from-P2;22.95;101',
      '10.2.3.20;from-P2;22.95;100',
      '10.2.3.30;from-P2;22.95;101'
    ]
    count => 1
  }
}

filter {
  dissect {
    mapping => {
      "message" => "%{from_ip};%{app};%{amount};%{loggedin_userid}"
    }
    convert_datatype => {
      "amount" => "float"
      "loggedin_userid" => "int"
    }
  }
  jdbc_streaming {
    statement => "select descr as description from ref.local_ips where ip = :substitute"
    parameters => {substitute => "[from_ip]"}
    target => "server"
    jdbc_user => "logstash"
    jdbc_password => "logstash??"
    jdbc_driver_class => "org.postgresql.Driver"
    jdbc_driver_library => "/elastic/tmp/postgresql-42.1.4.jar"
    jdbc_connection_string => "jdbc:postgresql://localhost:5432/ls_test_2"
  }
}

output {
  stdout {
    codec => rubydebug {metadata => true}
  }
}

Output:

{
                "app" => "from-P2",
           "sequence" => 0,
             "server" => [
        [0] {
            "description" => "Payroll Server"
        }
    ],
             "amount" => 22.95,
         "@timestamp" => 2018-08-07T15:36:02.284Z,
           "@version" => "1",
               "host" => "Elastics-MacBook-Pro.local",
    "loggedin_userid" => 101,
            "message" => "10.2.3.40;from-P2;22.95;101",
            "from_ip" => "10.2.3.40"
}
{
                "app" => "from-P2",
           "sequence" => 0,
             "server" => [
        [0] {
            "description" => "Payments Server"
        }
    ],
             "amount" => 22.95,
         "@timestamp" => 2018-08-07T15:36:02.304Z,
           "@version" => "1",
               "host" => "Elastics-MacBook-Pro.local",
    "loggedin_userid" => 100,
            "message" => "10.2.3.20;from-P2;22.95;100",
            "from_ip" => "10.2.3.20"
}
{
                "app" => "from-P2",
           "sequence" => 0,
             "server" => [
        [0] {
            "description" => "Events Server"
        }
    ],
             "amount" => 22.95,
         "@timestamp" => 2018-08-07T15:36:02.305Z,
           "@version" => "1",
               "host" => "Elastics-MacBook-Pro.local",
    "loggedin_userid" => 101,
            "message" => "10.2.3.30;from-P2;22.95;101",
            "from_ip" => "10.2.3.30"
}