Conver String to date (or replace @timestamp) (SOLVED)

Elastic Stack Logstash
1

Hi All,

I would like to convert the "string" to "date" and replace @timestamp or "string" convert to type "date", but it seems does not work, am I missing something

following is my logstash conf setting

filter {
        ruby {
            code => "
                    event['stock_date'] = Date.parse(event['stock_date']);
            "
        }

        date {
                locale => "zh-TW"
                timezone => "Asia/Taipei"
                match => ["stock_date", "YYYY-MM-dd;HH:mm:ss.SSS"]
                target => "@timestamp"
        }
}

Result

"@timestamp" => "2015-10-26T07:08:30.440Z",
"stock_date" => #<Date: 2015-10-21 ((2457317j,0s,0n),+0s,2299161j)>,

Jason

2

Use the date filter to parse a string and convert it to an ISO8601 timestamp (usually stored in the @timestamp). No Ruby filter is necessary for this.

Note that what happens on the Elasticsearch side isn't really up to Logstash. Logstash sends JSON documents to ES, and scalars in JSON documents are strings, numbers, or booleans. To have a timestamp stored as a date in ES you need to change the mapping. This is done by default for the @timestamp field.

3

Hi Magnus,

Thanks for replying,

I tried

    date {
            locale => "zh-TW"
            timezone => "Asia/Taipei"
            match => ["stock_date", "YYYY-MM-dd HH:mm:ss.SSS", "ISO8601"]
            target => "@timestamp"
    }

output {
        elasticsearch {
                host => "140.92.25.42"
                #codec => plain { charset => "Big5" }
                codec => "json"
                protocol => "http"
                index => "logstash-%{+YYYY.MM.dd}"
        }

}

But the result is that the es create a index by each record, shortly, 1000 lines > 1000 indices

4

Let's first establish whether the date filter does its job. Is the timestamp in stock_date correctly converted to the @timestamp field? Pick some random documents and see what things looks like. Were those documents placed in the right index? For a document that ended up in the wrong index, what did stock_date and @timestamp look like?

5

  1. Pick some logstash result

             "@version" => "1",
       "@timestamp" => "20151019-12-31T16:00:00.000Z",
             "host" => "swagger4es",
             "path" => "/tmp/test10/HTC.txt",
             "type" => "json",
         "stock_no" => "2498",
       "stock_name" => "宏達電",
       "stock_date" => "20151020",

         "@version" => "1",
       "@timestamp" => "20121216-12-31T16:00:00.000Z",
             "host" => "swagger4es",
             "path" => "/tmp/test10/HTC.txt",
             "type" => "json",
         "stock_no" => "2498",
       "stock_name" => "宏達電",
       "stock_date" => "20121217",

         "@version" => "1",
       "@timestamp" => "20121212-12-31T16:00:00.000Z",
             "host" => "swagger4es",
             "path" => "/tmp/test10/HTC.txt",
             "type" => "json",
         "stock_no" => "2498",
       "stock_name" => "宏達電",
       "stock_date" => "20121213",

  2. After a while

    retrying failed action with response code: 429 {:level=>:warn}
    retrying failed action with response code: 429 {:level=>:warn}
    retrying failed action with response code: 429 {:level=>:warn}
    retrying failed action with response code: 429 {:level=>:warn}
    retrying failed action with response code: 429 {:level=>:warn}
    too many attempts at sending event. dropping: 20141112-12-31T16:00:00.000Z swagger4es 2498 宏達電

    20141113 136.09 139.07 136.09 137.58 7508 1042186 1.0949 0.9042 830352 115003 138.00 138.50 1.0889 0.448 1.666 4616 85.49 - 1 {:level=>:error} 0.61 0.00 0.00 1.50 TSE
    too many attempts at sending event. dropping: 20141116-12-31T16:00:00.000Z swagger4es 2498 宏達電 20141117 139.07 139.07 135.60 136.59 5245 723879 -0.7220 0.6317 830352 114173 137.00 137.50 -0.7246 0.450 1.090 3439 84.88 - 1 {:level=>:error} 0.61 0.00 0.00 -1.00 TSE
    too many attempts at sending event. dropping: 20141120-12-31T16:00:00.000Z swagger4es 2498 宏達電 20141121 136.09 136.59 134.60 134.60 3257 443501 -1.0949 0.3922 830352 112512 135.50 136.00 -1.1009 0.434 0.609 2147 83.64 - 1 {:level=>:error} 0.60 0.00 0.00 -1.50

Kibana (index pattern: logstash-*)

kibana_stock_question.png1299×345 29.9 KB

6

It looks like the date filter is doing the right thing, but maybe you haven't considered the consequences of parsing stock_date into @timestamp and using time-series indexes. If stock_date is spread out over long periods of time (which the 2012-12-13 date in your example indicates) you will get lots of indexes unless you reconfigure the elasticsearch output to not create one index per day.

Possible solutions to your problem include:

  • Don't parse stock_date' into@timestamp`.
  • Scale up your cluster to deal with the thousands of indexes that are bound to occur.
  • Don't use time-series indexes at all, or scale them down to one index per year or one index per month instead of one index per day.

What the best solution is depends on the specifics of what you want to accomplish, like e.g. how many documents you expect there to be per day (i.e. the number of unique stock_date values).

7

Hi Magnus

if 'don't parse stock_date into@timestamp', how to convert the field 'stock_date' to 'date' type?

I tried to modified output to one index
As far as I can search the data in es
curl -XGET 'http://140.92.25.42:9200/logstash-stock/_search?pretty'

{
      "_index" : "logstash-stock",
      "_type" : "json",
      "_id" : "AVCjoAbeFCj-RUraIpaD",
      "_score" : 1.0,
      "_source":{"message":["2498\t宏達電\t20120322\t551.69\t560.51\t547.27\t549.04\t5750\t3607614\t-0.4800\t0.6748\t852052\t529976\t622.00\t623.00\t-0.4812\t2.413\t3.908\t4707\t8.05\t8.19\t5.84\t5.84\t\t1.15\t6.03\t5.71\t-3.00\t\t\t\tTSE\r"],"@version":"1","@timestamp":"20120321-12-31T16:00:00.000Z","host":"swagger4es","path":"/tmp/test12/HTC.txt","type":"json","stock_no":"2498","stock_name":"宏達電","stock_beggin_val":"551.69","stock_high_val":560.51,"stock_low_val":"547.27","stock_end_val":"549.04","stock_tarns":"5750","stock_trans_val":"3607614","stock_reward_rate":"-0.4800","stock_inv_rate":"0.6748","stock_out":"852052","stock_value":"529976","stock_last_buy":"622.00","stock_last_sale":"623.00","stock_reward_ln":"-0.4812","stock_mark_rate":"2.413","stock_trans_rate":"3.908","stock_trans_count":"4707","stock_eps_tse":"8.05","stock_eps_tej":"8.19","stock_price_tse":"5.84","stock_price_tej":"5.84","stock_up_down_stop":null,"stock_r_tej":"1.15","stock_bonus_rate_tse":"6.03","stock_cash_rate":"5.71","stock_up_down":"-3.00","stock_a":null,"stock_d":null,"stock_y":null,"stock_market":"TSE"}
    }

But it cannot be found in Kibana

kibana_stock_question.png1557×570 50.8 KB

Jason

8

if 'don't parse stock_date into@timestamp', how to convert the field 'stock_date' to 'date' type?

You need to adjust the mappings of the index (probably through an index template).

But it cannot be found in Kibana

That's because the timestamp in your example looks like this:

@timestamp":"20120321-12-31T16:00:00.000Z

This mean Dec 31 of the year 20120321.

9

I try to create a template:

curl -XPUT 'http://localhost:9200/_template/logstash-stock' -d '{
    "template": "logstash-stock-*",
    "settings": {
        "number_of_shards": 1,
        "number_of_replicas": 0,
        "index.refresh_interval": "5s"
    },
    "mappings": {
        "stocks": {
            "properties": {
                "stock_date": {
                    "type": "date",
                    "format": "yyyy-MM-dd"
                },
                "stock_high_val": {
                    "type": "float"
                },
                "stock_low_val": {
                    "type": "float"
                },
                "stock_end_val": {
                    "type": "float"
                }
            }
        }
    }
}'

But after data into es by logstash, and create 'logstash-stocks-*' index pattern for kibana, then kibana shows

Mapping conflict! 4 fields are defined as several types (string, integer, etc) across the indices that match this pattern. You may still be able to use these conflict fields in parts of Kibana, but they will be unavailable for functions that require Kibana to know their type. Correcting this issue will require reindexing your data.

stock_date  	conflict 				
stock_high_val  	conflict 				
stock_low_val  	conflict 				
stock_end_val  	conflict

Am I missing something?

10

I don't know what to add to what Kibana already says. The listed fields are mapped in different ways in different indexes and Kibana has problems dealing with that. I suggest you reindex the old data.

11

Thanks for suggestion

finally I follow to "adjust mappings of the index (probably through an index template)." as you said, it can be work

12

I have Syslog timestamp from event parsed by logstash as : "syslog_timestamp" => "Jan 22 15:20:36"
How do I convert into timestamp / time format (instead of string) so that It can be searchable and I can apply time based query in Elasticsearch ?
I do this:
date {
target => "syslog_timestamp"
match => [ "syslog_timestamp", "MMM d HH:mm:ss", "MMM dd HH:mm:ss" ]
}

Still output is :
{
"syslog_timestamp" => "Jan 22 15:20:36",
"@timestamp" => "2016-01-22T23:20:36.000Z"
}

13

Looks good, assuming your timezone is UTC-8; keep in mind that @timestamp is UTC. That's not a problem since Kibana by default adjusts the @timestamp to the browser's timezone (and similar tools can trivially do the same thing).