Issues with dates/times that don't have a timezone (Logstash/Kibana)


(Scott Butler) #1

The basic issue is when I try and replace @timestamp with another date setting the timezone option all other dates are 5 hours off in Kibana because they are stored without a timezone and Kibana assumes they must be UTC.

More detail using sample data...

csv columns => [ "receive_time","generated_time","start_time"]
line would look like ( dates in EST / UTC -5 / -0500) = 2016/11/07 13:09:44,2016/11/07 13:09:39,2016/11/07 13:09:40

Since they are firewall syslog entries, I would prefer that @timestamp use "generated_time" for auditing purposes incase there is an issue with syslog and/or older logs need to be imported.

date {
  locale => "en"
  timezone => "America/New_York"
  match => [ "generated_time", "yyyy/MM/dd HH:mm:ss" ]
  tag_on_failure => ["_dateparsefailure"]
}

Here is where it breaks down...
The timestamp is converted to ISO8601(UTC) using the new date (2016/11/07 13:09:39) ...
"@timestamp" => "2016-11-07T18:09:39.000Z",

The other three dates are left as is...
"receive_time" => "2016/11/07 13:09:44",
"generated_time" => "2016/11/07 13:09:39",
"start_time" => "2016/11/07 13:09:40",

Because Kibana setting use the Browsers timezone offset (EST / UTC -5 in my case) all the other times are assumed UTC and then lose 5 hours.
2016/11/07 13:09:44 >> 2016/11/07 08:09:44

I have read so many posts now I don't know what the best solution is. I imagine if I can just add the -0500 timezone to my times then all should be good?

Do I do that at CSV, Mutate, or Date?
date {
timezone => "America/New_York"
match => [ "generated_time_orig", "yyyy/MM/dd HH:mm:ss" ]
target => "generated_time_fixed"
}

or

mutate {
replace => [ "generated_time", "%{generated_time}-05:00" ]
}

Not even sure if I have the mutate one right and I assume I have to have a "date" target for each time I want to fix.

Any assistance would be greatly appreciated,
Scott


(Andrew Cholakian) #2

If I understand you correctly it sounds like the issue is that some of the times are run through the date filter, using the timezone property to correct the offset. Have you tried running all time fields through the date filter? You can use the target setting in the date filter to have the date filter overwrite the start_time field, for instance, with the output of the date filter.


(Scott Butler) #3

You are understanding correctly, I am asking where the best place to fix each time's timezone is.

If I use the date filter, do I have to setup a separate one for each time to be fixed or can I do multiple matches/targets in one date filter? Also, you are saying I can just overwrite "start_time" I don't have to do a "start_time_orig" => "start_time_fixed"?

Thanks for your time and assistance,
Scott


(Andrew Cholakian) #4

Unfortunately no, we do not support that. You'll to use a date filter per field. That would be a welcome patch however!

You'll need to do something like:

date {
  timezone => "..."
  match => ["myfield"]
  target => ["myfield"]
}

For each field.


(Scott Butler) #5

Oh man, that might be a bunch of date filters for some of my logs. I would have thought appending the timezone using the mutate filter might be more efficient.

Thanks for your help.


(andrewvc) #6

@s33butler I think your use case makes sense. Can you open an issue on http://github.com/logstash-plugins/logstash-filter-date ? I think we should have a better way of handling your use case.


(Scott Butler) #7

Oh gosh, I'll see what I can do. Figure someone ran into this before, and was looking how best to tackle it. I didn't want to bring it up, but it gets real messy if you have syslogs from different timezones. Say I have firewalls in both UTC -5 and UTC -8, now I need 12 date filters as it is basically 6 x timezones for my scenario.


(Andrew Cholakian) #8

@s33butler this usually isn't an issue because usually people have timestamps with an included zone, or they don't have quite so many timestamps.

This could be a good place to use the ruby filter to judiciously convert dates to ISO8601 with zone.


(system) #9

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