Making Peace with Logstash Part 4 – Manipulating Dates

1

April 9, 2018 by Mike Hillwig

I’ve been slacking on my blogging again. The last few weeks have been busy, including a visit from Elastic Consulting. But it’s back to the flight data.

So far, I’ve done a decent job getting the data into shape. My biggest challenge, though, was the dates and times. Dates are in one field, and the times are in another. Dates look like 2014-02-26 and times look like 0852 Using a traditional datetime datatype would be nice to have, so I’ll have to do it myself. In order to turn a date and time into a datetime, I need to abut the two fields and then convert it.

I accomplished this by using a mutate filter, employing by several add_field commands. Notice how I simply abut the two times.

			mutate{
				add_field => {"ts" => "%{FlightDate}" }
				add_field => {"[@metadata][indexname]"  => "flights-%{+YYYY}" }
				add_field => {"[CRSDepTimeDT]" => "%{FlightDate} %{CRSDepTime}"}
				add_field => {"[CRSArrTimeDT]" => "%{FlightDate} %{CRSArrTime}"}				
				add_field => {"[DepTimeDT]" => "%{FlightDate} %{DepTime}"}
				add_field => {"[ArrTimeDT]" => "%{FlightDate} %{ArrTime}"}				
				add_field => {"[WheelsoffDT]" => "%{FlightDate} %{WheelsOff}"}
				add_field => {"[WheelsOnDT]" => "%{FlightDate} %{WheelsOn}"}

After that, it was as easy as running this through a date filter.

			date {
				match => [ "CRSArrTimeDT" , "yyyy-MM-dd HHmm" ]
				target => "CRSArrTimeDT" 
				locale => "en-US"
				}
			date {
				match => [ "DepTimeDT" , "yyyy-MM-dd HHmm" ]
				target => "DepTimeDT" 
				locale => "en-US"
				}

When executed, it looks like this:

As I looked at the dates, something was off, and I just couldn’t figure out what it was. When the date filter runs, it converts the timezome to UTC, and I’m in EST. Surely there has to be a way to get this right, too. Since I have the city name and airport code, there should be a way to convert that to a timezone. That’s a project for another day. The other thing I need to do is increment the date when a flight lands after midnight.

If you’re following along at home, this configuration file is in my GitHub repository.

Next, we’re going to start enriching the data so we can look at aircraft types.