Overwrite @timestamp filed by date and time filed in the CSV file

Elastic Stack Logstash
1

My logstash conf. file is like the below:

input {
  file {
    path => "/somepath/balance_20220731.csv"
    start_position => "beginning"
    sincedb_path => "/dev/null"
  }
}
filter {
  csv {
      separator => ";"
      skip_header => "true"
      columns => ["date","time","ActorId", "ClosingBalance"]
  }
  date {
        add_field => { "ndate" => "%{date} %{time}" }
        match => ["ndate", "yyyy-MM-dd HH:mm:ss"]
        target => "@timestamp"
        remove_field => "ndate"
  }
  mutate {
      convert => {
          ClosingBalance => "integer"
      }
          remove_field => ["message","path","host","@version"]
  }
}
output {

stdout {}

}

m csv file format is like:

Date;time;ActorId;ClosingBalance
2022-07-31;00:00:00;somename;91003700

I am trying to overwrite @timestamp field value with date and time field values that come from the csv file. but i am getting document timestamp in the @timestamp field.

following is my parrsed output:

{
              "time" => "00:00:00",
        "@timestamp" => 2022-08-01T16:01:56.718590Z,
             "event" => {
        "original" => "2022-07-31;00:00:00;Api-ASANApp;68590000"
    },
              "date" => "2022-07-31",
           "ActorId" => "Api-ASANApp",
               "log" => {
        "file" => {
            "path" => "/somepath/balance_20220731.csv"
        }
    },
    "ClosingBalance" => 68590000
}

any help plz

2

This is wrong, the add_field will only be executed if the date filter is successful, which will not happen because the ndate file does not exist.

You should first create the ndate field using a mutate filter and then use the date filter.

Try this:

  mutate {
        add_field => { "ndate" => "%{date} %{time}" }
  }
  date {
        match => ["ndate", "yyyy-MM-dd HH:mm:ss"]
        target => "@timestamp"
        remove_field => "ndate"
  }
3

Thanks @leandrojmp, I changed my conf file as suguested:

input {
  file {
    path => "/somepath/balance_20220731.csv"
    start_position => "beginning"
    sincedb_path => "/dev/null"
  }
}
filter {
  csv {
      separator => ";"
      skip_header => "true"
      columns => ["date","time","ActorId", "ClosingBalance"]
      convert => {
          "ClosingBalance" => "integer"
        }
  }
  mutate {
        add_field => { "ndate" => "%{date} %{time}" }
  }
  date {
        match => ["ndate", "yyyy-MM-dd HH:mm:ss"]
        target => "@timestamp"
        #remove_field => "ndate"
  }
  mutate {
      convert => {
          ClosingBalance => "integer"
      }
          remove_field => ["message","path","host","@version"]
  }
}
output {

stdout {}

but @timestamp field still remain same:


               "log" => {
        "file" => {
            "path" => "/somepath/balance_20220731.csv"
        }
    },
             "event" => {
        "original" => "2022-07-31;00:00:00;SuperWifi;19990000"
    },
             "ndate" => "2022-07-31 00:00:00",
           "ActorId" => "SuperWifi",
              "time" => "00:00:00",
    "ClosingBalance" => 19990000,
              "date" => "2022-07-31",
        "@timestamp" => 2022-07-30T19:30:00Z
}
4

It does not seem that it is stays the same since it is not using the current time, it looks like that it got the 2022-07-31 00:00:00 time and shifted it by 4:30 hours, this would happen if you are in the timezone of UTC+04:30 and your Logstash is configured to use this timezone.

In which timezone are you in? And what is the timezone of your date time?

All dates in logstash and elasticsearch will always be in UTC, if your log time is not in UTC you need to explicitly configure the timezone in the date filter.

It would be something like this example:

  date {
        match => ["ndate", "yyyy-MM-dd HH:mm:ss"]
        target => "@timestamp"
        timezone => "+0430"
        remove_field => "ndate"
  }

In this case I'm telling Logstash that the value of the ndate field is a date and time from a timezone with UTC+04:30.

5

Hello @leandrojmp,
Yes i am in zone UTC+04:30 which is the zone for the date and time fields in my csv file.
I have explicitly configure the timezone in the date filter but still the @timestamp date is not as it is there in ndate field. plz see the below output:

{
             "event" => {
        "original" => "2022-07-31;00:00:00;Geopol;5335822"
    },
             "ndate" => "2022-07-31 00:00:00",
    },
           "ActorId" => "Geopol",
              "date" => "2022-07-31",
              "time" => "00:00:00",
        "@timestamp" => 2022-07-30T19:30:00Z,
    "ClosingBalance" => 5335822
}
6

Thanks a lot @leandrojmp, i fixed it by adding timezone => "UTC" in the filter date ..

7

If the date and time of your files are in UTC + 04:30, you should not use the timezone as UTC, it needs to be timezone => "+0430".

The date and times in Elasticsearch are stored in UTC, but Kibana will convert it into the Timezone of the Browser, which normally is your timezone.

If the time 2022-07-31 00:00:00 is in UTC+04:30, then the UTC time is 2022-07-30 19:30:00.

8

Hello @leandrojmp ,
can you plz help me here:
https://discuss.elastic.co/t/extract-time-from-txt-file-and-date-from-file-name-and-add-them-to-timestamp-field/311196

9

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