Hi there,
As you can imagine reading the title, I'm using the unofficial jdbc output plugin to put logs into postgres.
I'm facing 2 problems, and I was wandering somebody else has experienced the same issues.
The first problem is, givin' the example configuration below, if the logstash event is not matching any value from the specified "%{example}" the output will fill out the column exactly with this value. That's not possible, if there isn't the field, then it should be null.
The second problem is: using the UPSERT function, what about if a receive a log that doesn't have all the field I want what but it has, for example, 2 value that it will update (let's say username and date), the other fields are empty(don't exist in the log). That will result that the UPDATE will update the corresponding field(empty) from the table?
input
{
http_poller {
urls => {
test1 => {
url => "http://localhost:9201/dp_api-*/_search?q=operation.result.http_status:200%20AND%20(event:druid.subscription.signup%20OR%20event:druid.subscription.activate%20OR%20event:bank.user.patch%20OR%20event:bank.legal.patch)&size=400"
method => get
}
}
request_timeout => 60
# Supports "cron", "every", "at" and "in" schedules by rufus scheduler
schedule => { every => "1m"}
codec => "plain"
#metadata_target => "http_poller_metadata"
}
}
filter {
json{
source => "message"
remove_field => ["message"]
}
split {
field => "[hits][hits]"
remove_field => [ "[hits][hits]" ,"_shards", "hits", "took", "timed_out", "@version"]
target => "body"
}
mutate {
remove_field => ["[body][_index]", "[body][_type]"]
}
ruby {
code => "
event.get('body')['_source'].each {|k, v|
if k != '@timestamp'
then event.set(k,v)
end
}
event.set('[@metadata][_id]', event.get('body')['_id'])
event.remove('body')
event.remove('_score')"
}
date {
match => ["ts", "ISO8601","dd/MMM/YYYY:HH:mm:ss Z","MMM d HH:mm:ss"]
add_tag => ["to_enrich"]
}
grok {
match => { "ts" => "%{DATE_EU:date}T%{TIME:time}" }
}
alter {
coalesce => [
"status", "%{[resource][response_attributes][user][status]}", "%{[resource][response_attributes][status]}", "%{[resource][response_attributes][legal_user][status]}", "%{event}"
]
}
alter {
coalesce => [
"firstname", "%{[resource][response_attributes][firstname]}","%{[resource][request_attributes][user][firstname]}"
]
}
alter {
coalesce => [
"lastname", "%{[resource][response_attributes][lastname]}","%{[resource][request_attributes][user][lastname]}"
]
}
}
output {
stdout{
codec => rubydebug
}
jdbc {
driver_jar_path => '/home/vittorio/jars/postgresql-42.1.1.jre6.jar'
connection_test => false
connection_string => 'jdbc:postgresql://127.0.0.1:5432/postgres'
statement => ["
INSERT INTO users VALUES(?,?,?,?,?,?)
on conflict (username,date)
do update set (date,status, firstname, lastname, last_update) = (?,?,?,?,?)
where users.username = ? "
, "%{[request][username]}","%{date}","%{status}", "%{firstname}", "%{lastname}","%{time}","%{date}","%{status}", "%{firstname}", "%{lastname}","%{time}","%{[request][username]}"
]
username => "postgres"
password => "password"
}
}
Hope I explained well and really hope somebody can help me 