How to handle empty result in jdbc_streaming filter plugin in Logstash

[naveen007 (Neo)]

Hi,
I'm using the jdbc_streaming filter plugin to get data from query. But in some case query return empty result or null value.

parameters => { "firstName" => "firstName" }              
statement =>  "SELECT DISTINCT TOP 1 userid  FROM employee where first_name=:firstName "                    
target => "user"

Below is the query result if data is not empty.

 "user" => [
        [0] {
            "userid" => 1240932
        }

Then I will get it by using below code
add_field => { "userId" => "%{[user][0][userid]}" }
But in case empty or null result Logstash through exception. Below is the code

tags" => [
        [0] "_jdbcstreamingdefaultsused"
    ],

I want to set empty if Target object (user) is empty or null. I have tried below code but it is not working.

 if [user][0]{
              mutate{
                   add_field => { "userId" => "%{[user][0][userid]}" }       
                   remove_field => ["user"]
                  }
          }else{
            mutate {
                   
                   add_field => { "userId" => "" } 
                   remove_field => ["user"]

             }
          }

Can anybody tell me how to handle this?

[Badger]

Does setting

default_hash => { "userid" => "" }

help? You should be able to do the mutate unconditionally then.

[naveen007 (Neo)]

I am not getting your point. Where should I write this code and what about condition ?
I want to check user is empty or not. Below is the output when query return empty

 "user" => [
        [0] {}
]

Please let me know if you have any queries.

[Badger]

If you set the default_hash option on the jdbc_streaming filter then if no rows match it will return that default hash in the target array. So the array will always exist and you can do your

mutate{
    add_field => { "userId" => "%{[user][0][userid]}" }       
    remove_field => ["user"]
}

unconditionally.

[naveen007 (Neo)]

Got it. Thanks, it's working for me.