Join Data from a database to an event based on field value

[Simon_Becker]

Hi everyone,

i have a pretty complicated problem to solve:
I would like enrich my datastream that I recieve from applications with metadata. I need this later to create on joined fields user restrictions and so on.
Every customer needs his own index. Problem is that in my original data I have to join customer names on the hostname, based on a lookup to then forward the data to different indices.

I had a look at the Jdbc_streaming filter which seems to be the right choice.
But I cannot find any condition parameter on my values.

My goal is to join on a hostname values like "customer", "location" [...].
Therefore I need a condition to say if hostname is server1.domain add location:berlin and customer:elastic.
Pipeline could be like

input{
http{
host => 123.xy
port => 8080
}
}
filter{
#here I have no idea what to do
#DB connection
#Then how to join on a field
}
output{
elasticsearch{
index=index%customer%
ssl =>enabled
user => "myuser"
passwordd => "mysupersecretpassword123!"
}
}

I have a lot of servers, so i do not want to write for every server a condition.

In case the JDBC connection is not the right choice for this use case, i could switch to a csv file an try it with this.

Has anyone in the community made experience with something like this?

Thank you all

[Badger]

If you create a YAML file containing lines like

server1.domain: { "location": "berlin", "customer":"elastic" }

then you use a translate filter like this:

    translate {
        field => "hostname"
        dictionary_path => "/home/user/foo.yml"
    }

That will get you an event like

   "hostname" => "server1.domain",
"translation" => {
    "customer" => "elastic",
    "location" => "berlin"
},

If you want to move the fields from "translation" to the top level then you could use

    if [translation] {
        ruby { code => 'event.get("translation").each { |k, v| event.set(k,v) }; event.remove("translation")' }
    }

[Simon_Becker]

Thank you, worked!