Fields Date dd.mm.YYYY and Time 00:00:00 unable to map from String to Date formatted


(Thorben) #1

Hi all,

I'm new to the Elastic Stack and do my first steps.

While all is going well Beats > Logstash > ES > Kibana
I'm fighting with an obviously easy question but hard for me to
figure out how to.

Date and Time from String to formatted as Date

Beats yml just got the path of the csv file.
input_type: log

Following logstash.config:

input {
beats {
port => "5044"
}
}
filter {
csv {
columns => ["Date","Time","Country"]
separator => "|"
quote_char => "~"
}
mutate {
split => { "message" => "|" }
}
mutate {
add_field => {
"Date" => "%{[message][0]}"
}
}
mutate {
add_field => {
"Time" => "%{[message][2]}"
}
}
mutate {
add_field => {
"Country" => "%{[message][3]}"
}
}
}
output {
elasticsearch {
hosts => ["localhost:9200"]
index => ["test"]
}
}

Now I check the mapping in ES of Data and Time in Kibana:
Time is = 0,285659722222222 = String
Date = 10.01.2019 = String

Hmm,
Ok do date on it:

date {
match => [ "Date", "dd.MM.YYYY" ] (tried also with : - / \ and spaces)
target => "Date"
}

date {
match => [ "Time", "HH:mm:ss" ] (tried also with . - / \ and spaces)
target => "Time"
}

Still the same:
Time is = 0,285659722222222 = String
Date = 10.01.2019 = String

The why:
I use the field "Date" in Controls as Option List, but the sort is strange clustered Ascending day's to month and year.
I need Recent date always up (Desc) or bottom (Asc) as usual like in Excel etc.

Kibana | What I need (Desc or Asc)
01.01.2019 | 09.01.2019
02.01.2019 | 08.01.2019
03.01.2019 | 07.01.2019
04.01.2019 | 06.01.2019
05.01.2019 | 05.01.2019
06.01.2019 | 04.01.2019
07.01.2019 | 03.01.2019
08.01.2019 | 02.01.2019
09.01.2019 | 01.01.2019
01.12.2018 | 11.12.2018
02.12.2018 | 10.12.2018
03.12.2018 | 09.12.2018
04.12.2018 | 08.12.2018
05.12.2018 | 07.12.2018
06.12.2018 | 06.12.2018
07.12.2018 | 05.12.2018
08.12.2018 | 04.12.2018
09.12.2018 | 03.12.2018
10.12.2018 | 02.12.2018
11.12.2018 | 01.12.2018

What I'm doing wrong?
Hope you can help
Thanka and Brgds
Thorben