Filtering data from database


#1

I am taking data from database tables .how to filter this data ?can i filter


(Magnus Bäck) #2

Yes.

For a more detailed answer, please ask a more detailed question. Are you using the jdbc input? What kind of filtering are you looking for?


#3

yes i am using jdbc input plugin
my conf file is like this

file: contacts-index-logstash.conf

input {
jdbc {
jdbc_connection_string => "jdbc:oracle:thin:@//host:1521/orcl.newgen.co.in"
jdbc_user => "od6oracle"
jdbc_password => "od6oracle"
jdbc_validate_connection => true
jdbc_driver_library => "D:\logstash-2.0.0\Easy Search\ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.OracleDriver"
statement => "select * from pdbdocument"
}
}
filter {
mutate { add_field => { "showme" => "This data will be in the %{author}output" } }
}
output {
elasticsearch {
index => "testbyme"
document_type => "mine"
hosts => "127.0.0.1:9200"
}
}


#4

It indexes into elasticsearch but showme field is not there .


#5

Actually mysql query will fetch a table from database and that table will contain columns like address ,city,street .
by filtering these values i want to make a json object like this
"address":{
"city":{
"name":{"cityname"}
"street":{
"name":{"streetname"}
}
}
}


(Magnus Bäck) #6

I don't know why you're not getting a showme field. As for the rest, use a mutate filter and its rename option to move the fields. Untested:

mutate {
  rename => {
    "city" => "[address][city][name]"
    "street" => "[address][city][street]"
  }
}

Keep in mind that if you already have a field named address you can't create a nested field to it. You may have to rename address to something else first to get it out of the way.


#7

What you mean by that i should already have field address ?
I have address,city,street columns in my table that i fetched from database using jdbc input . and i want json like

{
"address":
{
"city":
{
"city":"cityname",
"street":"streetname"

    }
}

}


(Magnus Bäck) #8

As you're saying, you already have a field called address. Then you can't add a nested field with that name as you have in your example, but if you first rename address to e.g. address_ you can use the mutate filter in my example.


#9

Ok but as there are two values in the city how can that be achieved .


(Magnus Bäck) #10

The mutate filter example that I posted produces what you're asking for.

I have updated it after realizing there were a few typos.


#11

I am getting string not matched error in mutate filter :frowning:

[31mException in filterworker, the pipeline stopped processing new events, please
check your filter configuration and restart Logstash. {"exception"=>#, "backtrace"=>["org/jruby/RubyString.java:3910:in `[]='


(Magnus Bäck) #12

If you post your configuration and an example input message maybe we can help.


#13

input {

jdbc {
jdbc_connection_string => "jdbc:oracle:thin:@//host:port/orcl.newgen.co.in"
jdbc_user => "od6oracle"
jdbc_password => "od6oracle"
jdbc_validate_connection => true
jdbc_driver_library => "D:\logstash-2.0.0\Easy Search\ojdbc7.jar"
jdbc_driver_class => "Java::oracle.jdbc.OracleDriver"
statement => "select author,name,commnt,appname
from pdbdocument"
}

}

filter {
mutate {
rename =>{
"commnt" => "[name][commnt]"
"appname" => "[commnt][author]"
}

}
}
output {
elasticsearch { hosts => "localhost:9200" }
stdout { codec => rubydebug }
}

this is my configuration file .


(Magnus Bäck) #14

Please also post an example input message (i.e. a message emitted by the jdbc input, one that you want to filter) as requested earlier.


#15

author appname commnt name
supervisor html bank mydoc
alice jpg no comment documnet

this is the table format output by jdbc and i want to filter it .


(Magnus Bäck) #16

The following minimal example works and produces the output you seem to want:

$ cat test.config 
input { stdin { codec => "json" } }
output { stdout { codec => "rubydebug" } }
filter {
  mutate {
    rename => {
      "commnt" => "[name][commnt]"
      "appname" => "[commnt][author]"
    }
  }
}
$ cat data
{"commnt": "this is a comment", "appname": "this is an appname"}
$ /opt/logstash/bin/logstash -f test.config < data
Logstash startup completed
{
      "@version" => "1",
    "@timestamp" => "2015-11-18T15:04:51.738Z",
          "host" => "lnxolofon",
          "name" => {
        "commnt" => "this is a comment"
    },
        "commnt" => {
        "author" => "this is an appname"
    }
}
Logstash shutdown completed

#17

Thankyou so much magnus :smile: it worked !
One thing more !
I have two strings like
Str1-Name,age,country,company
Str2- myname,22,india,newgen
And i want to create json pairs like this
"Name":"myname"
"Age":22
"Country":" india"
"Company":" newgen"
And after this i already have a json like this this
Address:{
}
And i want to insert these above json pairs to insert into it
Address:{
Name":"myname"
"Age":22
"Country":" india"
"Company":" newgen"
}
Please suggest how to do this .As i can not find any loop to do this .
Please Reply as soon as possible i am stucked :frowning:


(Magnus Bäck) #18

Look into the csv filter and use its target option to create the fields as subfields of Address.


#19

input {
stdin {
}
}

filter {
csv {

    columns => ["dataclassname", "age","message"]
    separator => ","
   
    
    
}
ruby {
    code => '
        b = event["message"].split("#");
        ary = Array.new;
        for c in b;
            keyvar = c;
            d = "" << keyvar <<" ";
            ary.push(d);
        end;
        event["lastColum"] = ary;
    '
}

mutate
{
rename=>
{
"dataclassname" =>"[dataclass][datclassname]"
 
}
    
}
csv
{
   source =>"age"
   separator=>"&"
   columns=>ary
    target=>"[dataclass][fields]"
    
}

}

output {
elasticsearch { hosts => "localhost:9200" }
stdout { codec => rubydebug }
}

this is my conf file .here is an array i declared into ruby filter named ary and i am passing this array to csv filter as it takes in the column an array .but its not working .?


(Magnus Bäck) #20

I don't understand what you're trying to do. You claim to take the results of the ruby filter and pass it to you csv filter but that's impossible since the ruby filter executes after the csv filter. Are you trying to get the csv filter to pick up the column names from an array field in the same message?