CSV field from string to Date

Elastic Stack Logstash
1

Hi,

I have to convert the Run_date of the CSV file as date but its populating as string. Please help me..!!

Sample CSV file;
30-NOV-17,GH,381,CUSTOMERS
30-NOV-17,GH,50047,TRANSACTIONS
30-NOV-17,IQ,0,TRANSACTIONS
30-NOV-17,KE,631,CUSTOMERS
30-NOV-17,KE,95374,TRANSACTIONS

Below is the config file -

cat logstconf.conf
input {
file {
path => "/tmp/filename.csv"
type => "csv"
start_position => "beginning"
sincedb_path => "/dev/null"
}
}
filter {
csv {
separator => ","
columns => ["Run_date","Cntry_code","Count","Cust_OR_TRAN"]
skip_empty_columns => true

}

date {
match => ["Run_date", "dd-MMM-yy"]
}

mutate{
convert => { "Count" => "integer" }
}
mutate{
add_field => {
"application" => "appnme"
}
}
mutate{
add_field => {
"Environment" => "prd"
}
}

}
output {
elasticsearch{
action => "index"
#hosts => "http://localhost:9200"
hosts => "http://localhost:9200"
index => "indexname"
workers => 1

}
stdout {
    codec => rubydebug
}

}

2

I think you need to add "target => "Run_date" to the date filter.

3

Thanks.. will try and let you know..

4

hi,

I have tried the target as below.

date {
match => ["Run_date", "dd-MMM-yy"]
target => "Run_date"
}

good news is its accept the Run_date as date. but its capturing the time in 24hrs format.

below is the Logstash output.
{
"path" => "filename.csv",
"@timestamp" => 2017-12-18T05:37:52.874Z,
"Cust_OR_TRAN" => "CUSTOMERS",
"@version" => "1",
"Environment" => "PRD",
"Run_date" => 2017-12-05T16:00:00.000Z,
"Count" => 1225,
"message" => "06-DEC-17,ZM,1225,CUSTOMERS",
"type" => "csv",
"Cntry_code" => "ZM"
}

I'm expecting the output as like as in the message "06-DEC-17". but actually it takes as " "Run_date" => 2017-12-05T16:00:00.000Z,"

Please help.!!

5

The date filter parses strings into UTC timestamps with millisecond precision. Presumably your timezone is UTC+8, and 2017-12-06T00:00:00,000+0800 => 2017-12-05T16:00:00,000+0000 so things look fine.

6

Hi,

thanks.! can you help me to get only the date in dd-MMM-yy format [as like as in CSV.]

the problem why I want to convert is, I'm unable to sort the date as asend / descn while I visualize the same in Kibana. because this capture as a string..

7

You can change the way the date is displayed in Kibana in it's settings. In Kibana under "Management" -> "advanced settings" -> dateForm

8

Hi.. I have changed the settings in advanced settings and now I can get the date in kibana like DD-MMM-YY. now the problem is as I mentioned earlier the date is converted based on TZ and shows the different date.

original ---
"message" => "06-DEC-17,ZM,1225,CUSTOMERS", conversation ---
"Run_date" => 2017-12-05T16:00:00.000Z,

Pls help me here..!!

9

The configuration you posted is not applying a timezone. Are you applying the correct timezone in Logstash in the date filter?

10

No.. I'm not configuring any timezone.. it takes default I believe....

11

Date fields are always stored in UTC, so it's only normal they are displayed that way in JSON. When your local time is 06-DEC-17 and logstash adds 00:00 for hours and minutes, the UTC time for that is 2017-12-05T16:00:00.000Z. Kibana is smart enough to translate that back to your local time, and so should any other program.

12

Can you help me to configuring kibana to translate back to local time please...!! I just modified the display option in advanced settings like DD-MMM-YY...

13

The display option has nothing to do with this. The timezone adjustment option ("dateFormat:tz") is set to "browser" by default and that's the behavior you're looking for.

14

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