CSV date parser failure while uploading it to logstash

Elastic Stack Logstash
1

Hi , I am stuck in parsing the date field in CSV and tried my best but no luck.

CSV contains 2 fields, Data and Responsetime (separated by comma) and I want to plot graph in KIBANA with x axis as Date and response as Y axis. I am able to load the data to elastic search through logstash but it is failing with Date parsing.

Sample format of CSV
2017-04-20 10:00:01.789,2
2017-04-20 10:00:03.061,2
2017-04-20 10:00:07.247,2
2017-04-20 10:00:07.471,2

Conf file for uploading, filtering and output

input {
file {
path => "/home/ubuntu/Logsheet3.csv"
type => "sportium"
start_position => "beginning"
}
}
filter {
csv {

columns => ["date","responsetime"]
separator => ","
}
date {
match => ["date","YYYY-MM-dd HH:mm:ss.SSS","ISO8601"]
target => "@timestamp"
}
mutate {
convert => [ "responsetime", "float" ]
}
}
output {
elasticsearch {
hosts => ["localhost:9200"]
index => "sportiumcsv"
}
stdout { codec => rubydebug }
}

Jsonoutput in elasticsearch

{
"_index": "sportiumcsv",
"_type": "sportium",
"_id": "AVvYodnHbFL5xGFaJABg",
"_version": 1,
"_score": null,
"_source": {
"date": "2017-04-20 10:59:59.192,1",
"path": "/home/ubuntu/Logsheet3.csv",
"@timestamp": "2017-05-05T12:42:17.394Z",
"@version": "1",
"host": "ip-172-31-1-97",
"message": ""2017-04-20 10:59:59.192,1"\r",
"type": "sportium",
"tags": [
"_dateparsefailure"
]
},
"fields": {
"@timestamp": [
1493988137394
]
},
"sort": [
1493988137394
]
}

2

Why is there a trailing ",1" in the date field? And where's the responsetime field? Aha. Your message field is double-quoted, so the csv filter (correctly) assumes that the input contains a single column. You can use the mutate filter and its gsub option to remove the quotes, but it would be better to fix the problem at the source.

3

Thanks Magnus

CSV file as 2 column data- Date and responsetime which is separated by comma. I will try to correct the CSV file content to get ride of the double quotes and let you know whether it worked out.

Thanks,
Manju

4

Hi Magnus,

I tried below gsub mutate it didn't work, can you please guide me here.

I found the source from : http://stackoverflow.com/questions/22534325/syntax-for-a-grok-mutate-gsub-to-replace-double-quotes-with-single-quotes/22548499#22548499

I am not replacing with single quotes but trying to get rid of double quotes inserted by CSV

filter {
mutate {
gsub => ["message",""",""]
}
}

Below is full conf file data, but no luck

input {
file {
path => "/home/ubuntu/Logsheet3.csv"
type => "sportium"
start_position => "beginning"
}
}

filter {
csv {

columns => ["timestamp","responsetime"]
separator => ","
}
mutate {
gsub => ["message",""",""]
convert => [ "responsetime", "float" ]
}
date {
match => ["timestamp", "YYYY-MM-dd HH:mm:ss.SSS", "ISO8601"]
target => "@timestamp"
}
}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "sucess-%{+YYYY.MM.dd}"
}
stdout { codec => rubydebug }
}

Thanks,
Manju

5

Try this:

gsub => ['message', '"', '']
6

I tried it but still had issues. So corrected at source by opening the CSV in notepad++ and removed the double quotes and saved it. then used it below code it worked perfectly!!

input {
file {
path => "/home/ubuntu/Logsheet4.csv"
type => "Success"
start_position => "beginning"
}
}

filter {
csv {
columns => ["timestamp","responsetime"]
separator => ","
}
mutate {
convert => [ "responsetime", "float" ]
}
date {
match => ["timestamp", "YYYY-MM-dd HH:mm:ss.SSS", "ISO8601"]
target => "@timestamp"
}
}

output {
elasticsearch {
hosts => ["localhost:9200"]
index => "succesful-%{+YYYY.MM.dd}"
}
stdout { codec => rubydebug }
}

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.