# Jdbc\_streaming filter help

**URL:** <https://discuss.elastic.co/t/jdbc-streaming-filter-help/142499>\
**Category:** Logstash\
**Created:** [August 1, 2018, 8:00am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-help/142499 "2018-08-01T08:00:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ripopo](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@ripopo](https://discuss.elastic.co/u/ripopo)\
**Post date:** [August 1, 2018, 8:00am UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-help/142499/1 "2018-08-01T08:00:01Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [August 7, 2018, 3:53pm UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-help/142499/2 "2018-08-07T15:53:31Z")

</div>

> [@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.

```auto
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:

```auto
{
                "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"
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 4, 2018, 3:53pm UTC](https://discuss.elastic.co/t/jdbc-streaming-filter-help/142499/3 "2018-09-04T15:53:37Z")

</div>

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