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.

Answer 2

With Holger's answer, i fixed mine. (thanks!)

He is completely right about the conversion. Ticks are a weird time unit :P I won't repeat his perfectly fine explanation here.

This will parse the String (!!!) tick value and create a Logstash time object to write into the @timestamp field:

ruby {
      init => "require 'time'"
      code => "
        ts = (event['message'].to_i - 621355968000000000) / 10000000
        event['@timestamp'] = LogStash::Timestamp.new(Time.at(ts))
       "
    }

Fun problem :) Still think Holger deserves the tick ;)

Note: you don't need the "require time" part. It is already imported when the lambda executes in logstash.

Note2: logstash 5.0 breaks the event (becomes a java bean I believe), so instead of accessing it as an array you need to access it via a set method.

Regards,

Artur

P.s. example:

artur@pandaadb:~/dev/logstash$ ./logstash-2.3.2/bin/logstash -f conf2/
Settings: Default pipeline workers: 8
Pipeline main started
636062509024728064
{
       "message" => "636062509024728064",
      "@version" => "1",
    "@timestamp" => "2016-08-08T11:01:42.000Z",
          "host" => "pandaadb"
}

P.s.2: I think the "can't dup Fixnum" was from me trying to call parse on an integer value (not sure though)

Answer 3

This will parse the String and tick value and create a Logstash time object to write into the @timestamp field.

Note: event.set and event.get used for retrieval and assign data in logstash

if [logmessage.logtime]
{
    ruby 
    {
        code => "event.set('@timestamp', LogStash::Timestamp.at((event.get('logmessage.logtime').to_i - 621355968000000000) / 10_000_000.0))"
    }
}