Getting hour value from @timestamp in a custom 'hour' field


#1

Hi all,

I'm currently tring to get the hour of my document's timestamp in a custom 'hour' field.

I did some research and found the following solution but the resulting value in the hour field is always 5 hour higher than the actual hour of the @timestamp field.

This is the pertinent config so far:

 date {
                match => [ "timestring", "yyyy-MM-dd HH:mm:ss" ]
                target => "@timestamp"
                }
                mutate {
                add_field => {"[hour]" => "%{+HH}"}
                add_field => {"[minute]" => "%{+mm}"}
                add_field => {"[weekday]" => "%{+EEE}"}
                remove_field => [ "message" ]
                }

timestring is parsed by Grok from my log lines to replace the @timestamp field from the documents for accurate support of old logs. this should be pretty standard and it's working correctly.

the value returned by %{+HH} is always 5h over the hour from my timestamp. It might be because i;m in Canada/Eastern time (-5) and kibana is usting UTC.

On the other hand, maybe %{+HH} value's is not taken from the @timestamp field, which i assumed to be the default behaviour. Maybe it's returning the actual hour from the logstash service (which would also break the old-log support since the value is not from the parsed lines (not tested this yet))

I found the add_field snippet on stackoverflow (https://stackoverflow.com/questions/32222491/extract-specific-time-field-from-timestamp-in-logstash) but i have no idea hot to set a timezone or substract 5 hours to the value inside the add field clause.

Can anybody suggest a working way to get an hour/minute/day of the week field from the @timestamp of the document? Maybe i could have different target fields after the match and use some options to put only the wanted value (HH, mm, EEE) into the target fields. Or i could simply set a timezone somewhere?

Thanks!


(Magnus B├Ąck) #2

the value returned by %{+HH} is always 5h over the hour from my timestamp. It might be because i;m in Canada/Eastern time (-5) and kibana is usting UTC.

That's probably what's going on. The @timestamp field is always UTC but Kibana will (by default) adjust to the browser's local timezone.

On the other hand, maybe %{+HH} value's is not taken from the @timestamp field

It is taken from @timestamp.

if you want the hours in local time you need to use a ruby filter.


#3

Thanks a lot!


#4

I have a question about add_field => {"[hour]" => "%{+HH}"} , how does it save this new field as an string or as an integer?. I've been trying to find it in the documentation but I have been unable to find it.

I have to make a similar change but i need to have the value of the hour as an Integer as it is a better solution for my problem.


#5

Finally i decided to format my documents directly from my php app and send it with bulk api to ES directly so i'm not much help on the subject but i ran in a similar problem when creating my documents (which i fixed by removing quotes around the value in my json string) and i made the following "painless" script to update existing documents having the value as a string in them:

I'm aware it is off topic and not a direct solution to your question, but it might come in handy in your situation.

update by query with script adding field hourInt to documents parsing hour field (string) value:
{ "query": { "exists" : { "field" : "hour" } } "script": { "lang": "painless", "source": "ctx._source['hourInt'].value = Integer.parseInt(ctx._source['hour'].value)" } }

it might work too if you just replace the string value of hour to an int value in the same field but i'm not sure about the behaviour with the field mapping (so i created a new one, hourInt)


(system) #6

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