Logstash timestamp issue - the log line date is one hour ahead the actual date

Elastic Stack Logstash
1

Hello, I'm trying to read input from JDBC and applied date filter but logstash logs is one hour ahead the actual date. I don't where this difference came from.

Logstash conf file

input {
jdbc {
jdbc_driver_library => "DDD"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "DDD"
jdbc_user => "SSS"
jdbc_password => "SSS"

statement => "SELECT rec_time,rejectInfo_rejectCause,rejectInfo_moRoutingRule,rejectInfo_mtRoutingRule,sccpCgPa_sccpAddress_globalTitle_number,responseInfo_queryResult,responseInfo_submissionResult,responseInfo_deliveryResult,outboundMt_sriSmResponseInfo_queryResult,outboundMt_mtFwdSmToMscResponseInfo_deliveryResult,storage_storageResult,outboundAt_responseInfo_deliveryResult,ecResponseData_textInEvaluationResponse,messageIdentifier_msgId  from lgp_inb_msg_${SMSC_DATE} where rec_time > :sql_last_value"
use_column_value => true
clean_run => true
schedule => "0 * * * *"
tracking_column => "rec_time"
}
}

filter {

    date {
        match => [ "rec_time", "MMM D, YYYY @ HH:mm:ss.SSS" ]
        target => ["@timestamp"]
    }


}

output {
    elasticsearch {
        hosts => ["XXXX:9200"]
        index => "DFG-%{+YYYY.MM.dd}"
        user => "DDD"
        password => "DDD"
        document_id => "%{rec_no}%{id_file}"
    }
}

logstash log sample:

[2020-08-09 @ 15:00:01.336][INFO ][logstash.inputs.jdbc     ][main]

But actually the current time stamp is 2 PM so the logging is ahead by one hour.
Really spent much time to figure where this difference came from with no luck.
Thanks in advance for your help.

logstash version is 7.8

2

Does this post help? Your date filter does not set the timezone, so it will be assuming that what came out of the database is UTC.

3

Thanks Badger for your reply.
I tried all the below with no luck:

    date {
        match => [ "rec_time", "MMM D, YYYY @ HH:mm:ss.SSS" ]
        timezone => "EET"   ### timestamp still ahead by one hour
     #  timezone => "UTC"   ### timestamp still ahead by one hour
     #  timezone => "Africa/Cairo"   ### timestamp still ahead by one hour
     #  timezone => "UTC-1"   ### gives error
       target => ["@timestamp"]
    }
4

If you use

output { stdout { codec => rubydebug } }

what do rec_time and @timestamp look like?

5

HYG
"@timestamp" => 2020-08-09T14:55:05.512Z,
"timestamp" => "20200809160751+0200",
"rec_time" => 2020-08-09T14:07:51.000Z,

Where the system timestamp is 4:55 PM

6

OK, so when the jdbc sets [rec_time] it is setting it to UTC. Note that there are no quotes around the value of rec_time, so the input has converted it to a Logstash::Timestamp. It is not a string so the date filter cannot parse it.

You could

mutate { convert => { "rec_time" => "string" } }

and the date filter would be able to parse it. The timezone option will be ignored because that trailing Z on the value of [rec_time] says it is already UTC.

You should not have to set the timezone option, but if you decide you need to you can

mutate { gsub => [ "rec_time", "Z$", "" ] }

to enable it.

7

I'm really sorry annoying with that but still can't get the right logging timestamp in the log file.
I got the below format from rubydebug

     "timestamp" => "20200809182425+0200",
      "rec_time" => "2020-08-09T16:24:25.000",
    "@timestamp" => 2020-08-09T17:25:09.229Z,

And the config file

input {
jdbc {
DDDD
}
}
filter {

    mutate {
        convert =>
          { "rec_time" => "string"
          }
           }
    mutate { gsub => [ "rec_time", "Z$", "" ] }
    date {
        match => [ "rec_time", "MMM D, YYYY @ HH:mm:ss.SSS" ]
        timezone => "UTC"
        target => ["@timestamp"]
    }
}

output {
stdout { codec => "rubydebug" }
}

And still see the logstash log file

[2020-08-09T20:25:01,308][INFO ][logstash.inputs.jdbc     ][main]

which is ahead the actual time by one hour

8

That is not even close to the actual format of rec_time. Try "YYYY-MM-dd'T'HH:mm:ss.SSS"

9

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