CODEWITHSUNDEEP
elasticsearchlogstash
h.l.m
h.l.m

Reputation: 13465

using logstash to get data out of elasticsearch to a csv file

I am new to using logstash and am struggling to get data out of elasticsearch using logstash as a csv.

To create some sample data, we can first add a basic csv into elasticsearch... the head of the sample csv can be seen below

$ head uu.csv
"hh","hh1","hh3","id"
-0.979646332669359,1.65186132910743,"L",1
-0.283939374784435,-0.44785377794233,"X",2
0.922659898930901,-1.11689020559612,"F",3
0.348918777124474,1.95766948269957,"U",4
0.52667811182958,0.0168862169880919,"Y",5
-0.804765331279075,-0.186456470768865,"I",6
0.11411203100637,-0.149340801708981,"Q",7
-0.952836952412902,-1.68807271639322,"Q",8
-0.373528919496876,0.750994450392907,"F",9

I then put that into logstash using the following...

$ cat uu.conf 
input {
  stdin {}
}

filter {
  csv {
      columns => [
        "hh","hh1","hh3","id"
      ]
  }

  if [hh1] == "hh1" {
      drop { }
  } else {
      mutate {
          remove_field => [ "message", "host", "@timestamp", "@version" ]
      }

      mutate {
          convert => { "hh" => "float" }
          convert => { "hh1" => "float" }
          convert => { "hh3" => "string" }
          convert => { "id" => "integer" }
      }
  }
}

output {
  stdout { codec => dots }
  elasticsearch {
      index => "temp_index"
      document_type => "temp_doc"
      document_id => "%{id}"
  }
}

This is put into logstash with the following command....

$ cat uu.csv | logstash-2.1.3/bin/logstash -f uu.conf 
Settings: Default filter workers: 16
Logstash startup completed
 ....................................................................................................Logstash shutdown completed

So far so good, but I would like to get some of the data out in particular the hh and hh3 fields in the temp_index.

I wrote the following to extract the data out of elasticsearch into a csv.

$ cat yy.conf
input {
  elasticsearch {
    hosts => "localhost:9200"
    index => "temp_index"
    query => "*"
  }
}

filter {
    elasticsearch{
        add_field => {"hh" => "%{hh}"}
        add_field => {"hh3" => "%{hh3}"}
    }
}


output {
  stdout { codec => dots }
  csv {
      fields => ['hh','hh3']
      path => '/home/username/yy.csv'
  }
}

But get the following error when trying to run logstash...

$ logstash-2.1.3/bin/logstash -f yy.conf
The error reported is: 
  Couldn't find any filter plugin named 'elasticsearch'. Are you sure this is correct? Trying to load the elasticsearch filter plugin resulted in this error: no such file to load -- logstash/filters/elasticsearch

What do I need to change to yy.conf so that a logstash command will extract the data out of elasticsearch and input into a new csv called yy.csv.

UPDATE

changing yy.conf to be the following...

$ cat yy.conf
input {
  elasticsearch {
    hosts => "localhost:9200"
    index => "temp_index"
    query => "*"
  }
}

filter {}

output {
  stdout { codec => dots }
  csv {
      fields => ['hh','hh3']
      path => '/home/username/yy.csv'
  }
}

I got the following error...

$ logstash-2.1.3/bin/logstash -f yy.conf
Settings: Default filter workers: 16
Logstash startup completed
A plugin had an unrecoverable error. Will restart this plugin.
  Plugin: <LogStash::Inputs::Elasticsearch hosts=>["localhost:9200"], index=>"temp_index", query=>"*", codec=><LogStash::Codecs::JSON charset=>"UTF-8">, scan=>true, size=>1000, scroll=>"1m", docinfo=>false, docinfo_target=>"@metadata", docinfo_fields=>["_index", "_type", "_id"], ssl=>false>
  Error: [400] {"error":{"root_cause":[{"type":"parse_exception","reason":"Failed to derive xcontent"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"init_scan","grouped":true,"failed_shards":[{"shard":0,"index":"temp_index","node":"zu3E6F7kQRWnDPY5L9zF-w","reason":{"type":"parse_exception","reason":"Failed to derive xcontent"}}]},"status":400} {:level=>:error}
A plugin had an unrecoverable error. Will restart this plugin.
  Plugin: <LogStash::Inputs::Elasticsearch hosts=>["localhost:9200"], index=>"temp_index", query=>"*", codec=><LogStash::Codecs::JSON charset=>"UTF-8">, scan=>true, size=>1000, scroll=>"1m", docinfo=>false, docinfo_target=>"@metadata", docinfo_fields=>["_index", "_type", "_id"], ssl=>false>
  Error: [400] {"error":{"root_cause":[{"type":"parse_exception","reason":"Failed to derive xcontent"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"init_scan","grouped":true,"failed_shards":[{"shard":0,"index":"temp_index","node":"zu3E6F7kQRWnDPY5L9zF-w","reason":{"type":"parse_exception","reason":"Failed to derive xcontent"}}]},"status":400} {:level=>:error}
A plugin had an unrecoverable error. Will restart this plugin.

Interestingly...if i change yy.conf to remove elasticsearch{} to look like...

$ cat yy.conf
input {
  elasticsearch {
    hosts => "localhost:9200"
    index => "temp_index"
    query => "*"
  }
}

filter {
        add_field => {"hh" => "%{hh}"}
        add_field => {"hh3" => "%{hh3}"}
}


output {
  stdout { codec => dots }
  csv {
      fields => ['hh','hh3']
      path => '/home/username/yy.csv'
  }
}

I get the following error...

$ logstash-2.1.3/bin/logstash -f yy.conf
Error: Expected one of #, { at line 10, column 19 (byte 134) after filter {
        add_field 
You may be interested in the '--configtest' flag which you can
use to validate logstash's configuration before you choose
to restart a running system.

Also when changing yy.conf to be something similar to take into account the error message

$ cat yy.conf
input {
  elasticsearch {
    hosts => "localhost:9200"
    index => "temp_index"
    query => "*"
  }
}

filter {
        add_field {"hh" => "%{hh}"}
        add_field {"hh3" => "%{hh3}"}
}


output {
  stdout { codec => dots }
  csv {
      fields => ['hh','hh3']
      path => '/home/username/yy.csv'
  }
}

I get the following error...

$ logstash-2.1.3/bin/logstash -f yy.conf
The error reported is: 
  Couldn't find any filter plugin named 'add_field'. Are you sure this is correct? Trying to load the add_field filter plugin resulted in this error: no such file to load -- logstash/filters/add_field

* UPDATE 2 *

Thanks to Val I have made some progress and started to get output. But they don't seem correct. I get the following outputs when running the following commands...

$ cat uu.csv | logstash-2.1.3/bin/logstash -f uu.conf 
Settings: Default filter workers: 16
Logstash startup completed
....................................................................................................Logstash shutdown completed

$ logstash-2.1.3/bin/logstash -f yy.conf
Settings: Default filter workers: 16
Logstash startup completed
....................................................................................................Logstash shutdown completed

$ head uu.csv
"hh","hh1","hh3","id"
-0.979646332669359,1.65186132910743,"L",1
-0.283939374784435,-0.44785377794233,"X",2
0.922659898930901,-1.11689020559612,"F",3
0.348918777124474,1.95766948269957,"U",4
0.52667811182958,0.0168862169880919,"Y",5
-0.804765331279075,-0.186456470768865,"I",6
0.11411203100637,-0.149340801708981,"Q",7
-0.952836952412902,-1.68807271639322,"Q",8
-0.373528919496876,0.750994450392907,"F",9

$ head yy.csv
-0.106007607975644E1,F
0.385395589205671E0,S
0.722392598488791E-1,Q
0.119773830827963E1,Q
-0.151090510772458E1,W
-0.74978830916084E0,G
-0.98888121700762E-1,M
0.965827615823707E0,S
-0.165311094671424E1,F
0.523818819076447E0,R

Any help would be much appreciated...

Upvotes: 0

Views: 3739

Answers (1)

Val
Val

Reputation: 217254

You don't need that elasticsearch filter, just specify the fields you want in your CSV in the csv output like you did and you should be fine. The fields you need in your CSV are already contained in the event, you simply need to list them in the fields list of the csv output, simply as that.

Concretely, your config file should look like this:

$ cat yy.conf
input {
  elasticsearch {
    hosts => "localhost:9200"
    index => "temp_index"
  }
}

filter {
}

output {
  stdout { codec => dots }
  csv {
      fields => ['hh','hh3']
      path => '/home/username/yy.csv'
  }
}

Upvotes: 1

Related Questions