Logstash timestamp error when CEST is at the end

Elastic Stack Logstash
1

Hi all,

I have a weird timestamp problem and maybe someone can help on this:

The PostgreSQL logs are in CSV format and I get a fixed date string from Postgres with CEST at the end.
How do I need to set the timestamp expression in the date filter to catch/accept CEST?

An example log message:
2015-08-21 17:02:22.753 CEST,"demo","demo",18372,"XXX.XXX.YYY.ZZZ:51716",55d73c07.47c4,3,"idle",2015-08-21 16:56:07 CEST,,0,LOG,00000,"Verbindungsende: Sitzungszeit: 0:06:15.000 Benutzer=demo Datenbank=demo Host=XXX.XXX.YYY.YYY port=51716",,,,,,,,,""

An example error message from Logstash (does not correspond to the message above but it would have the same meaning):
{:timestamp=>"2015-08-21T16:41:02.152000+0200", :message=>"Failed parsing date from field", :field=>"timestamp", :value=>"2015-08-21 16:41:01.373 CEST", :exception=>"Invalid format: "2015-08-21 16:41:01.373 CEST" is malformed at "CEST"", :config_parsers=>"YYYY-MM-DD HH:mm:ss.SSS z", :config_locale=>"default=en_US", :level=>:warn}

The corresponding logstash.conf snippet:
...
filter {
...
if [type] =~ "postgres" {
csv {
columns => [ "timestamp", "postgres_username", "postgres_databasename", "postgres_process_id", "postgres_connection_from", "postgres_session_id", "postgres_session_line_number", "postgres_command_tag", "postgres_session_start_time", "postgres_virtual_transaction_id", "postgres_transaction_id", "postgres_error_severity", "postgres_sql_state_code", "postgres_message", "postgres_detail", "postgres_hint", "postgres_internal_query", "postgres_internal_query_pos", "postgres_context", "postgres_query", "postgres_query_pos", "postgres_location", "postgres_application_name" ]
}
date {
match => [ "timestamp", "YYYY-MM-DD HH:mm:ss.SSS z" ]
}
}
...

Thx in advance,
Michael

2

Quoting the Joda-Time documentation:

Zone names: Time zone names ('z') cannot be parsed

So, unless you can hardcode the timezone in the date filter configuration I think you need to translate the timezone names into offsets and include the offset in the timestamp to be parsed.

3

THX for the info. I will think about a solution later. At the moment the entries are there with two seconds difference and we can live with that at the moment.

4

There seems to be some kind of fix for this, http://stackoverflow.com/questions/13181970/joda-datetime-parse-date-with-cest-in-string/13182191#13182191

But I'm not sure which version of joda-time Logstash uses?

5

Here is my fix:

First I grok the logline and separate the datetime, timezone and the rest.

grok {
  match => ['message', "%{DATA:timestamp} %{WORD:tz} ........"]
}

In my zone the 'tz' variable now contains 'CET' or 'CEST'.

With the translate filter I lookup the corresponding offset.
Note: you need to extend the dictionary with your own relevant zones + offsets.

translate {
  field       => 'tz'
  destination => 'tz_num'
  dictionary  => [
    'CET',   '+0100',
    'CEST',  '+0200'
  ]
}

Append the offset to the grokked timestamp:

mutate {
  replace => ['timestamp', '%{timestamp} %{tz_num}']
}

And now parse it as usual:

date {
  match => [ 'timestamp', 'yyyy-MM-dd HH:mm:ss.SSS Z' 
}

And as bonus:

mutate {
  remove => [ 'tz', 'tz_num' ]
}

Profit.

Hope this helps?

2 Likes
6

This helped me a lot when parsing Cisco logs.

Unfortunately Cisco doesn't have support for numbered timezones:S