Csv and date filter

Elastic Stack Logstash
1

Hi, i have a logstash agent setup on a window box with below configuration.
input {
file {
type => "autolog"
path => "E:/elkcluster/log/.log"
}
file {
type => "testscv"
path => "e:/elkcluster/test/.*"
}
}
filter {
if( [type] == "testscv")
{
csv {
columns => [ "id", "name", "job", "location", "joindate", "dummy" ]
separator => ","
}
}
}
output {
redis { host => "xxx.xxx.xxx.xxx" data_type => "list" key => "logstash" }
}

here is the sample csv file, and it worked with all fields parsed nicely.
1,aaaa,eng,l2,Oct 11 2010,1
2,bbb,mgr,l5,Oct 28 2010,1
3,ccc,mgr,l2,Nov 12 2013,1
4,ddd,eng,l4,Nov 10 2014,1
5,eee,eng,l2,Nov 18 2010,1
6,fff,eng,l2,Dec 12 2009,1

then i wanted to set the timestamp to the joindate, i added in the date filter below, but i got exception in elasticsearch. what should be the correct date format in this case? thank you.

filter {
if( [type] == "testscv")
{
csv {
columns => [ "id", "name", "job", "location", "joindate", "dummy" ]
separator => ","
}
date {
match => [ "joindate", "MMM dd YYYY" ]
}
}
}

[2015-07-22 12:00:46,456][DEBUG][action.bulk ] [logstash-2009.12.11][2] failed to execute bulk item (index) index {[logstash-2009.12.11][testscv][AU6z67dysgFpH4c5oQD7], source[{"message":["6,fff,eng,l2,Dec 12 2009,1\r"],"@version":"1","@timestamp":"2009-12-11T16:00:00.000Z","host":"xxx","path":"e:/elkcluster/test/empdata9.csv","type":"testscv","id":"6","name":"fff","job":"eng","location":"l2","joindate":"Dec 12 2009","dummy":"1"}]}
org.elasticsearch.index.mapper.MapperParsingException: failed to parse [joindate]

2

Why not delete the joindate field since you're parsing the string and storing it in @timestamp anyway?

date {
  match => [ "joindate", "MMM dd YYYY" ]
  remove_field => ["joindate"]
}

I'd also delete the message field.

3

Great, it works:smile:, thank you.
But why do we need to remove this field to make it work?

I actually tried another option by changing the format to "YYYY-MM-dd HH:mm:ss", and manipulated the joindate in sample data to have time, and it worked as well, without configuring the remove_field.

4

I'm not sure why ES attempts to parse that field as a date, but obviously if we remove the field that ES chokes on things work better.

I actually tried another option by changing the format to "YYYY-MM-dd HH:mm:ss", and manipulated the joindate in sample data to have time, and it worked as well, without configuring the remove_field.

Yes, but because of the reasons stated above that shouldn't be surprising.

Now you'll have two timestamp fields; @timestamp and joindate. If you're not using the date filter anymore to parse joindate and store it in @timestamp then @timestamp will instead be the time you ingested the data. That may or may not make sense, depending on how you're going to use the data.

5

thank you very much for the explanation !

6

hi magnus,
I am new to ELk.i have used the same date filter but index file is not generating in elastic search and am unable to see data in kibana. here is my config file
plz help me out.

7

@setlem, please start a new thread for your question.