Parsing the date and sending to ElasticSearch

Elastic Stack Logstash
1

Hello everyone,

Great to be here :slight_smile:

For hours am trying to parse a file from filebeat and send it to ES. It works, but the date field that is created in ES is set to string, instead of date.

I am using (Mac):
ES: 5.3.0
Filebeat: 5.3.0
Logstash: 5.3.0

This is the line that is send from Filebeat:

[2017-04-26 09:40:32] request.INFO: Matched route "home_logged_in". {"route_parameters":{"_controller":"AppBundle\Controller\HomeLoggedInController::showAction","_locale":"de","_route":"home_logged_in"},"request_uri":"https://something.com"}

This is the Logstash portion that parses this line:

if [@metadata][type] == "prod" or [@metadata][type] == "qaprod"{
grok {
match => { "message" => "%{TIMESTAMP_ISO8601:logdate}" }
}
date {
match => [ "logdate", "ISO8601" ]
. target => "logdate"
}
}

Now, when I look in the Kibana for the logdate, I see it exactly like this 2017-04-26 09:40:32. Problem is it is set as string. I need it as date.

Can you please help me?

2

I am running out of solutions. I have checked all similar problems in the forum to no avail.
I have tried many different things, and it always ends up in ES as string.
This is the last thing I tried:

if [@metadata][type] == "prod" or [@metadata][type] == "qaprod"{
   grok {
    match => { "message" => "%{TIMESTAMP_ISO8601:logdate}" }
   }
   mutate {
    convert => [ "logdate", "string" ]
    add_field => { "pleasework" => "%{logdate}" }
   }
   date {
    timezone => "Europe/Berlin"
    match => [ "pleasework", "ISO8601", "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]
    #target => "pleasework"
    }
  }

And of course, please work is still in string format.

For all I care, it can be set to timestamp as well.

3

A string that has been successfully processed by the date filter should be recognized as a date by ES. However, this recognition only happens the first time the field is seen. What do the index's mappings look like? Please post the output of a get mapping API call. Please also post an example document (preferably via copy/paste from the JSON tab in Kibana).

4

Thank you very much for your time!. I really appreciate it.
I always delete the index before sending new data to it. So it is always starting from scratch.
Here is JSON copy from the current Kibana. Please note there are some other errors, as I need to apply more GROK filters to it. I will do that, as soon as I manage to fix this date issue.
I really do not need a special date field, I am happy if I can map logdate to @timestamp field.

From Kibana:

"type": "fesotprod",
"tags": [
"_jsonparsefailure",
"beats_input_codec_json_applied",
"_dateparsefailure"
],
"@timestamp": "2017-04-27T15:57:43.297Z",
"logdate": "2017-04-26 09:40:33",
"@version": "1",
"beat": {
"hostname": "C700893",
"name": "C700893",
"version": "5.3.0"
},
"host": "C700893",
"fingerprint": "844563e8094c0c1810c04b3347155ad4f0082dff"
},
"fields": {
"@timestamp": [
1493308663297
]
}

Two sample string from the original log (log starts with the date):

2017-04-26 09:40:33] security.DEBUG: Stored the security token in the session. {"key":"_security_secured_area"}
[2017-04-26 09:50:42] request.INFO: Matched route "home_logged_in". {"route_parameters":{"_controller":"AppBundle\Controller\HomeLoggedInController::showAction","_locale":"de","_route":"home_logged_in"},"request_uri":"https://qa.someserver.de/de/home"}

Get mapping call on the index after was created (logdate is the problematic field):

{
"fesotprod": {
"mappings": {
"fesotprod": {
"properties": {
"@timestamp": {
"type": "date"
},
"@uuid": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"beat": {
"properties": {
"hostname": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"fingerprint": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"host": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"input_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"logdate": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"message": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"offset": {
"type": "long"
},
"source": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"tags": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}
}

Thank you very much.

5

I think you have two options:

  • Fix your date filter so that it works and stores the result in logdate (if that really is where you want the timestamp stored).
  • Adjust the mappings of your index so that "2017-04-26 09:40:33" is recognized as a date.
6

Hello Magnus,

If I use this:

grok {
match => { "message" => "%{TIMESTAMP_ISO8601:logdate}" }
}
date {
timezone => "Europe/Berlin"
match => [ "logdate", "ISO8601", "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]
}

For these log lines:

2017-04-26 09:40:33] security.DEBUG: Stored the security token in the session. {"key":"securitysecured_area"}
[2017-04-26 09:50:42] request.INFO: Matched route "home_logged_in". {"route_parameters":{"controller":"AppBundle\Controller\HomeLoggedInController::showAction","locale":"de","route":"homelogged_in"},"request_uri":"https://qa.someserver.de/de/home"}

The way I understand it, it should overwrite the @timestamp. But it is not happening.

I am still getting it as a string in ES. You mentioned that I should fix my filter. I am really out of ideas now. Would you provide me some guidance?
:slight_smile:

7

Are you still getting the _dateparsefailure tag? If yes, look in your log for clues about why the date filter fails. It could be that the ISO8601 pattern doesn't match "2017-04-26 09:40:33" (because you have no "T" between the date and the time). A more exact pattern (similar to the second pattern you've listed) will definitely work.

8

I think I am on correct way. In my case, this is the pattern that should work:

%{YEAR}-%{MONTHNUM}-%{MONTHDAY} %{HOUR}:?%{MINUTE}(?::?%{SECOND})?

Now how do I use it in my case?

9

That's a grok pattern and we're talking about the date filter. Your second date pattern is "yyyy-MM-dd'T'HH:mm:ss.SSSZ" which is very close to what your logdate field looks like. It just needs a small adjustment.

10

I think I have constructed the pattern that works.
Here it is:

(?>\d\d){1,2}-(?:0?[1-9]|1[0-2])-(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9]) (?:2[0123]|[01]?[0-9]):(?:[0-5][0-9]):(?:(?:[0-5][0-9]|60)(?:[:.,][0-9]+)?)

I am confused now, how do I use it to extract the date in my case.

11

For the last time, stop modifying your grok filter. It was working earlier. Focus on the date filter. It's nearly correct but it needs a small adjustment to match what's in logdate (e.g. "2017-04-26 09:40:33"). Over and out.

12

For this string: [2017-04-26 15:23:52]

When I use this:

if [@metadata][type] == "fesotprod" or [@metadata][type] == "qafesotprod"{
grok {
match => { "message" => "%{TIMESTAMP_ISO8601:logdate}" }
}
date {
#timezone => "Europe/Berlin"
match => [ "logdate", "yyyy-MM-dd HH:mm:ss"]
}
}

I am getting a field created in ES. The field name is logdate, and it has value of 2017-04-26 15:23:52. But it is string.

Sorry if I piss you off somehow. Was not my intention.

13

Unless told otherwise with the target option the date filter stores the parsed timestamp in the @timestamp field.

14

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