Convert Ticks to @timestamp in logstash with Ruby-plugin

Question

I query every minute against a MSSQL Database with jdbc-plugin. In this Database my timestamp is stored in ticks. Field-name is lastupdate. Now I wanted to convert the lastupdate field to a timestamp-format and then overwrite the @timestamp field with the converted lastvalue field. I tried the ruby filter-plugin with method provided from another post on conversion from ticks to datetime (Parse ticks to datetime), but couldn't achieve the conversion and got _rubyexception and _dateparsefailure.

My filter looks like this:

filter {
  if [type] == "mydb" {
    ruby {
      init => "require 'time'"
      code => "lastupdate = Time.at(event['lastupdate'].to_s)"
    }
    date {
      match => [ "lastupdate", "MM/dd/YY HH:mm:ss,SSS" ]
    }
  }
}

Example for lastupdate: 636062509024728064

Date after conversion with http://tickstodatetime.com/: 2016-08-08​T11:01:42.472Z

Additional Info in Ticks: https://msdn.microsoft.com/en-us/library/system.datetime.ticks%28v=vs.110%29.aspx

Answer 1

The ticks value used here is specified as follows:

The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001 (0:00:00 UTC on January 1, 0001, in the Gregorian calendar), which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds.

In Ruby, the internal time representation follows the Unix convention of counting seconds since January 1, 1970, 00:00:00 UTC. Thus, to convert the value, you have to perform some additional steps. In expanded Ruby, this looks like this:

epoch = 621_355_968_000_000_000 # 1970-01-01T00:00:00Z in ticks
ticks_per_second = 10_000_000.0

lastupdate = 636062509024728064 # the value in ticks we would like to convert
seconds_since_epoch = (lastupdate - epoch) / ticks_per_second
Time.at(seconds_since_epoch).utc
# => 2016-08-08 11:01:42 UTC

In order to apply this to logstash, you can condense it a bit:

filter {
  if [type] == "mydb" {
    ruby {
      code => "event['@timestamp'] = LogStash::Timestamp.at((event['lastupdate'].to_i - 621355968000000000) / 10_000_000.0)"
    }
  }
}

This code snipped performs the calculation shown above and passes the result as an argument to Time.at. Finally, it takes the resulting time object, formats it in the format used by logstash and stores it in the @timestamp field.