Convert Elasticsearch Date/Time field from UTC To Local Time

Elastic Stack Logstash
1

I am query elasticsearch in my pipeline for a date/time field. Elasticsearch stores those date/time fields in UTC time, regardless of of the timezone they were ingested in. Is it possible to convert the time to local time?

Alternatively, can I take a local time value and convert it to utc? I'm using the two values to compare the time difference between them so I need them to be in the same zone.

2

Tell elasticsearch what timezone you want the results to be in as part of the query?

3

How do I do that? I see in the documentation I would use the time_zone parameter but where and how exactly? Do I have to use a query template or can I stick to using a query string?

4

There is an example in the documentation. I do not think you are forced to use a template.

5

Wait, let me back up, I think I went down the wrong rabbit hole. I am performing an elasticsearch query looking for a ticket number, say abc123. If that ticket number is found, then give me the field, updated_on, which is a date field stored in UTC time.

My understanding of what you are saying for me to do would not work because it assumes I am querying for a field value that is a date/time field, which I am not.

Or am I still misunderstanding and there is a way to have the requested fields returned in local time?

6

Here's my query config, if it helps:

  elasticsearch {
    hosts => ["https://fqdn:9200"]
   #The number field has an alphanumeric value.
    query => "number:%{number}"
    index => "servicenow-*"
   #The sys_updated_on field has a date/time value.
    fields => { "sys_updated_on" => "[old][sys_updated]" }
  }
7

Oh, right. In that case I would mutate+convert it to a string and run it through a date filter with a timezone option. It's not elegant, but it should work.

8

The value returned by elasticsearch is a date/time string, so I can't directly convert it (When I do, all I get is 2019). I can pass it to the date filter to convert it to a date/time object, then run a ruby code that converts it to unix time, but I'm still 5-6 hours off, depending on daylight savings time.

    #Convert elasticsearch returned string value into date/time data type
    date {
      match => [ "[old][sys_updated]", "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" ]
      target => "[old][sys_updated_time]"
    }
    #Convert date/time data type value into epoch seconds
    ruby { 
      code => 'event.set("[old][sys_updated_epoch]", event.get("[old][sys_updated_time]").to_i)'
    }
9

Strip off the Z with mutate+gsub before passing to a date filter with a timezone option?

10

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