Timestamp to epoch in microseconds - am I doing this right?

[Rubyan (Johan Van Der Kuijl)]

I'm reading events from an API that contains a field 'Timestamp' with a timestamp in the following format. The milliseconds are not always present or occur in 1, 2 or 3 digit resolution:

2017-07-11T11:55:05Z
2017-07-11T11:55:05.123Z
2017-07-11T11:55:05.12Z
2017-07-11T11:55:05.1Z

They have to be converted to epoch in microseconds. Therefore, I use this filter in Logstash:

    # extract the year, month, day and time including optional microseconds from the field Timestamp
    grok {
      match => [ "Timestamp", "%{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY:day}T%{TIME:time}" ]
    }
    
    # create a temporary field with a consistent formatted datetime
    mutate {
      add_field => {
        "[@metadata][timestamp_temp1]" => "%{year}-%{month}-%{day} %{time}"
      } 
    }        
    
    # create a date object that we can parse later with a ruby filter
    date {
      locale => "en"
      timezone => "UTC"
      match => ["[@metadata][timestamp_temp1]", "ISO8601", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss.SSS"]
      target => "[@metadata][timestamp_temp2]"
      remove_field => ["timestamp", "year", "month", "day", "time"]
    }  

    # convert the dateobject to an epoch in microseconds
    ruby { 
      code => "event.set('[@metadata][epoch_micro]', (1000000*event.get('[@metadata][timestamp_temp2]').to_f).round(0))" 
    }

The results:

    "@metadata" => {
                   "host" => "127.0.0.1",
        "timestamp_temp1" => "2017-08-04 11:55:05.234",
        "timestamp_temp2" => 2017-08-04T11:55:05.234Z,
            "epoch_micro" => 1501847705234000,
    }

Although the solution works, it took me a while to figure it out, and it feels a bit complicated. Is this the proper way to deal with dates or is there a more minimalistic solution?

[RohanKumbhar (R01K)]

You can try below conf

input { stdin{} }
filter {

  grok {
        match => {"message"=>"%{TIMESTAMP_ISO8601:mydate}"}
  }

  date {
        match => ["mydate", "ISO8601", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss.SSS"]
  }
  ruby { code => "event.set('epoc_timestamp', event.get('@timestamp').to_i)" }
}
output { stdout { codec => rubydebug } }

OUTPUT you will get

2017-07-11T11:55:05Z
{
      "@version" => "1",
          "host" => "localhost",
          "epoc" => 1499774105,
        "mydate" => "2017-07-11T11:55:05Z",
    "@timestamp" => 2017-07-11T11:55:05.000Z,
       "message" => "2017-07-11T11:55:05Z"
}
2017-07-11T11:55:05.123Z
{
      "@version" => "1",
          "host" => "localhost",
          "epoc" => 1499774105,
        "mydate" => "2017-07-11T11:55:05.123Z",
    "@timestamp" => 2017-07-11T11:55:05.123Z,
       "message" => "2017-07-11T11:55:05.123Z"
}
2017-07-11T11:55:05.12Z
{
      "@version" => "1",
          "host" => "localhost",
          "epoc" => 1499774105,
        "mydate" => "2017-07-11T11:55:05.12Z",
    "@timestamp" => 2017-07-11T11:55:05.120Z,
       "message" => "2017-07-11T11:55:05.12Z"
}
2017-07-11T11:55:05.1Z
{
      "@version" => "1",
          "host" => "localhost",
          "epoc" => 1499774105,
        "mydate" => "2017-07-11T11:55:05.1Z",
    "@timestamp" => 2017-07-11T11:55:05.100Z,
       "message" => "2017-07-11T11:55:05.1Z"
}

[Rubyan (Johan Van Der Kuijl)]

Thanks Rohan. I took the TIMESTAMP_ISO8601 of your solution and was able to minimize my config.

Note that a timestamp in MICROseconds epoch cannot be done by casting to int as you did, you have to cast to float and round the result, or you will loose some precision.

The final solution is now:

    # from the Timestamp field, extract the date into mydate
    grok {
      match => {"Timestamp"=>"%{TIMESTAMP_ISO8601:mydate}"}
    }

    # from the mydate field, contruct a date and store it in [@metadata][timestamp_temp]
    date {
      locale => "en"
      timezone => "UTC"    
      match => ["mydate", "ISO8601", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss.SSS"]
      target => "[@metadata][timestamp_temp]"      
    } 

    # calculate the micro second epoch from the timestamp_temp field and store it in [@metadata][epoch_event]
    ruby { 
      code => "event.set('[@metadata][epoch_event]', (1000000*event.get('[@metadata][timestamp_temp]').to_f).round(0))" 
    }  

[RohanKumbhar (R01K)]

Ok, good to hear that