Import csv file to ES but date isn't correct


(Thodoris Alexopoulos) #1

Hello,

I am trying to import a CSV file to the elasticsearch using logstash but i am facing a problem with the date.

csv file

2019-02-06 00 ,1.48,5.17,3.43,737.13,2049.12,1752.04
2019-02-06 01 ,1.80,7.14,4.41,762.62,2131.08,1825.97
2019-02-06 02 ,1.30,3.67,2.68,925.82,2099.62,1862.65

My logstash conf


input {
file {
path =>"/home/kibana/Downloads/scde1_report_MTSMS_2019-02-05.csv"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}
filter {
csv {
columns => [ "Date","Avg_TPS","Avg_Top_1%_TPS","Avg_Top_10%_TPS",
"Avg_Proc","Avg_Top_1%_Proc","Avg_Top_10%_Proc" ]
separator => ","

    }

date {
match => ["Date", "YYYY-MM-dd HH"]
timezone => "Europe/Athens"
target => "Date"
}

    mutate{
            convert => {
                    "Avg_TPS" => "float"
                    "Avg_Top_1%_TPS" => "float"
                    "Avg_Top_10%_TPS" => "float"
                    "Avg_Proc" => "float"
                    "Avg_Top_1%_Proc" => "float"
                    "Avg_Top_10%_Proc" => "float"
                    }
            }

}
output {
elasticsearch{
hosts => "localhost:9200"
index => "report"
document_type => "MT_SMS"
}
stdout{}

}

the output

{
"Date" => "Date",
"tags" => [
[0] "_dateparsefailure"
],
"path" => "/home/kibana/Downloads/scde1_report_MTSMS_2019-02-05.csv",
"host" => "kibana",
"Avg_TPS" => 0.0,
"Avg_Proc" => 0.0,
"Avg_Top_10%_TPS" => 0.0,
"Avg_Top_10%_Proc" => 0.0,
"@timestamp" => 2019-02-16T14:20:07.081Z,
"Avg_Top_1%_Proc" => 0.0,
"@version" => "1",
"Avg_Top_1%_TPS" => 0.0,
"message" => "Date,Hour,Avg_TPS,Avg_Top_1%_TPS,Avg_Top_10%_TPS,Avg_Proc,Avg_Top_1%_Proc,Avg_Top_10%_Proc",
"column8" => "Avg_Top_10%_Proc"
}
{
"Date" => "2019-02-05",
"tags" => [
[0] "_dateparsefailure"
],
"path" => "/home/kibana/Downloads/scde1_report_MTSMS_2019-02-05.csv",
"host" => "kibana",
"Avg_TPS" => 1.0,
"Avg_Proc" => 3.26,
"Avg_Top_10%_TPS" => 6.88,
"Avg_Top_10%_Proc" => 6984.3,
"@timestamp" => 2019-02-16T14:20:07.108Z,
"Avg_Top_1%_Proc" => 1104.64,
"@version" => "1",
"Avg_Top_1%_TPS" => 1.35,
"message" => "2019-02-05,01 ,1.35,6.88,3.26,1104.64,6984.30,3121.82",
"column8" => "3121.82"
}
{
"Date" => "2019-02-05",
"tags" => [
[0] "_dateparsefailure"
],
"path" => "/home/kibana/Downloads/scde1_report_MTSMS_2019-02-05.csv",
"host" => "kibana",
"Avg_TPS" => 2.0,
"Avg_Proc" => 3.27,
"Avg_Top_10%_TPS" => 7.0,
"Avg_Top_10%_Proc" => 2452.2,
"@timestamp" => 2019-02-16T14:20:07.108Z,
"Avg_Top_1%_Proc" => 916.24,
"@version" => "1",
"Avg_Top_1%_TPS" => 1.37,
"message" => "2019-02-05,02 ,1.37,7.00,3.27,916.24,2452.20,1989.51",
"column8" => "1989.51"
}

Any help will be appriciated!!


#2

Well .... no. That's not what your file looks like. The "message" fields in your output tell us that the format is

Date,Hour,Avg_TPS,Avg_Top_1%_TPS,Avg_Top_10%_TPS,Avg_Proc,Avg_Top_1%_Proc,Avg_Top_10%_Proc
2019-02-05,01 ,1.35,6.88,3.26,1104.64,6984.30,3121.82
2019-02-05,02 ,1.37,7.00,3.27,916.24,2452.20,1989.51

(system) closed #3

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