New help assigning a date to @timestamp

Elastic Stack Logstash
1

Hello,

NOOB here. Trying to assign a date that I have constructed from other fields in a csv to @timestamp. The field that is concatenated shows up in the imported data but I can get it into the @timestamp field. Here is the conf file segment.

Thanks for any advice you may be able to provide.

-Mark

filter {
if [type] == "l2tcsv" {
csv {
separator => ","
quote_char => "ª" # workaround: don't use a quote character as " gives issues if the field contains a "
columns => ["date","time","timezone","short"]
}
if [date] == "date" {
drop {} # drop the first line that contains the column names
}
mutate { merge => ["date", "time"] } # merge and join need to be in separate mutates
mutate { merge => ["date", "timezone"] } # merge and join need to be in separate mutates
mutate { join => ["date", " "] } # merge and join need to be in separate mutates
date {
match => ["date", "MM/dd/YYYY HH:mm:ss z" ]

target => "date"

}

}
}

What I get is two separate fields:

  1. @timestamp - the date load time
  2. date - which is the concatenation of the date, time, and timezone. I want that value in @timestamp
  3. short
2

date - which is the concatenation of the date, time, and timezone. I want that value in @timestamp

Then remove target => "date" in your date filter.

3

Magnus,

Thanks for your reply. That line is commented out so I don't think it is
impacting anything but I will remove it anyway.

date {
  match => ["date", "MM/dd/YYYY HH:mm:ss z" ]

target => "date"

}

Do you see anything else that could be affecting it?

Thanks,

-Mark

mobile:830.255.1551
Skype: mhallman

4

That line is commented out

Always format configuration and log snippets as preformatted text.

Do you see anything else that could be affecting it?

Please show an example event produced by Logstash. Use a stdout { codec => rubydebug } output.

5

{
"date" => "06/18/2016 02:29:52 UCT",
"path" => "/home/analyst/test-data/test.csv",
"@timestamp" => 2017-11-09T22:54:43.404Z,
"timezone" => "UCT",
"@version" => "1",
"host" => "ubuntu",
"short" => "URL: Cookie:pc user@www.radiorage.com/",
"time" => "02:29:52",
"message" => "06/18/2016,02:29:52,UCT,URL: Cookie:pc user@www.radiorage.com/",
"type" => "l2tcsv",
"tags" => [
[0] "_dateparsefailure"
]
}
{
"date" => "06/18/2016 02:32:41 UCT",
"path" => "/home/analyst/test-data/test.csv",
"@timestamp" => 2017-11-09T22:54:43.404Z,
"timezone" => "UCT",
"@version" => "1",
"host" => "ubuntu",
"short" => "URL: Cookie:pc user@eclkmpsa.com/adServe",
"time" => "02:32:41",
"message" => "06/18/2016,02:32:41,UCT,URL: Cookie:pc user@eclkmpsa.com/adServe",
"type" => "l2tcsv",
"tags" => [
[0] "_dateparsefailure"
]
}
{
"date" => "06/18/2016 04:00:00 UCT",
"path" => "/home/analyst/test-data/test.csv",
"@timestamp" => 2017-11-09T22:54:43.407Z,
"timezone" => "UCT",
"@version" => "1",
"host" => "ubuntu",
"short" => "[Empty description] D:\Nondeflagellated Cultures.xlsx",
"time" => "04:00:00",
"message" => "06/18/2016,04:00:00,UCT,[Empty description] D:\Nondeflagellated Cultures.xlsx",
"type" => "l2tcsv",
"tags" => [
[0] "_dateparsefailure"
]
}

6

I heard you about the preformatted text and thought I did it. Sorry if it did not come out that way.

7

As documented, time zone names can't be parsed. Is the time zone always UCT? If so use the date filter's timezone option.

8

Magnus,

Unfortunately, the timezone is variable.

-mh

9

Then you have two options:

  • Use a translate filter to, er, translate the time zone names into time zone offsets that the date filter accepts.
  • Put the time zone name in a field of its own and reference it in the timezone option of the date filter. This assumes that the time zone names being used are unambiguous and acceptable to the date filter (the filter documentation links to a table of supported time zone names).
10

Magnus,

I have a field in my csv, "l2t_timezone", but I am not having any luck
assigning is to timezone.

I have tried various ways like timezone => "l2t_timezone" with no success.

I have verified that the timezone names are supported. The timezone name
in my test data is "UCT".

Can you tell me how I would make that assignment?

Thank you.

-Mark

mobile:830.255.1551
Skype: mhallman

11

timezone => "%{l2t_timezone}", see https://www.elastic.co/guide/en/logstash/current/event-dependent-configuration.html#logstash-config-field-references.

12

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