CSV Date Filter not working

[kb2295]

Hello. I have a CSV file that looks a bit like this:

Date,Year,Month,Client,Execution Account,Source,SharedBook,I/E/H,FirmId,TraderLogin,Trader,Region,City,Exchange,ProductName,AssetClass,CTM,P&L,CFOXType,TradeType,HandleInst,Manual Fill,Strategy,HFT,Gratis,Sales Region,Legal Entity Region,Lots,OrderCount,FillCount,PerOfLots,PerOfOrders,PerOfFills,exchTraderId,execBrokerCode,tradingPlatform,responsiblePerson
"2017-09-20 00:00:00.0","2017","9","XXXX","TTTTT","ABC","FGH","E","XYZ","XYZ","XYZ","XYZ","XYZ","XYZ","XYZ-","ABs","GHI-00000","GHI","H","GHI","GHI","false","GHI","false","false","GHI","GHI","10","1","1","0","0","0","GH5555I","SSB","QQQ","PO0090"

I'm trying to use a .config file to upload the data. The "Date" field keeps showing up as a strign with the following configurations.

input {
file {
path => "/tmp/path/fakeMIScsv.csv"
start_position => "beginning"
sincedb_path =>"dev/null"
}

}
filter {
csv {
separator => ","
columns => ["Date","Year","Month","Client","Execution Account","Source","SharedBook","I_E_H","FirmId","TraderLogin","Trader","Region","City","Exchange","ProductName","AssetClass","CTM","P_L","CFOXType","TradeType","HandleInst","Manual Fill","Strategy","HFT","Gratis","Sales Region","Legal Entity Region","Lots","OrderCount","FillCount","PerOfLots","PerOfOrders","PerOfFills","exchTraderId","execBrokerCode","tradingPlatform","responsiblePerson"]
}
mutate{
convert => {
"Lots" => "integer"
"OrderCount" => "integer"
"FillCount" => "integer"
"PerOfLots" => "integer"
"PerOfOrders" => "integer"
"PerOfFills" => "integer"
}
}
date {
target => "Date"
match => [ "Date", "yyyy-MM-dd HH:mm:ss" ]
}
}

output {
elasticsearch {
hosts=> ["abc","xyz","abcd"]
index => "delete4"
user => *****
password => *****
ssl => true
ssl_certificate_verification => false
cacert => '/path/to/cert/ca.crt'
}
stdout {}
}

[yaauie (Ry Biesemeyer)]

Are the events also being tagged with _dateparsefailure?

I think I found the problem. The format string you have specified in your Date filter does not match the exact format of the value of the fields; the example you posted included sub-second precision, but the format string you provide doesn't.

date {
  target => "Date"
  match =>; [ "Date", "yyyy-MM-dd HH:mm:ss.S" ]
}

For more info on the acceptible date format strings, check out the docs.

[kb2295]

This fixed it. Thank you!