Replace @timestamp value with SQL DB datetime value

Elastic Stack Logstash
1

I have tried everything but nothing worked. I need to replace @timestamp with my Datetime column value.

Format of both values
Datetime: 2017-08-23T13:20:09.000Z
@timesatmp: 2018-03-13T12:11:38.080Z

input {
jdbc {
jdbc_driver_library => "C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://serverName:Port;user=user;password=password;database=DBNAME;"
jdbc_user => "user"
jdbc_password => "password"
statement => "SELECT 'BizTalk Application' As PlatformType,[ServiceName] AS Platform,[MachineName] AS HOST,[DateTime],[ExceptionMessage] As Message FROM [QAEsbExceptionDb].[dbo].[Fault]"
type => "esblogs"
}
beats{
port=> "5044"
}
}

filter {
mutate{
add_field => [ "Received_at", "%{@timestamp}"]
convert => [ "DateTime", "string" ]
}
date{
match => [ "DateTime", "YYYY-MM-dd HH:mm:ss.SSSZ" ]
target => "@timestamp"
}
}

output{
stdout { codec => rubydebug }
}

2

That does not match the format you have given for DateTime. Try

match => [ "DateTime", "YYYY-MM-dd'T'HH:mm:ss.SSS'Z'" ]
1 Like
3

I tried this but still no luck.

    "datetime" => 2017-11-03T10:18:54.000Z,
        "host" => "BTSBPMQA01",
        "type" => "esblogs",
    "@version" => "1",
"PlatformType" => "BizTalk Application",
  "@timestamp" => 2018-03-13T14:41:29.067Z,
4

Field names are case sensitive. Try

match => [ "datetime", "YYYY-MM-dd'T'HH:mm:ss.SSS'Z'" ]
5

As the datetime field is not formatted as a string (no surrounding ") in the rubydebug output, it could already be date object. You may be able to just copy it over without using the date plugin.

6

I tried this as well. But didn't work:
mutate
{
copy=> [ "timestamp", "datetime"]
}

Output:

  "datetime" => 2017-11-03T10:19:00.000Z,
      "host" => "BTSBPMQA01",
      "type" => "esblogs",
  "@version" => "1",
"PlatformType" => "BizTalk Application",
"@timestamp" => 2018-03-13T15:23:42.031Z,

and when i tried with this:
mutate
{
copy=> [ "@timestamp", "datetime"]
}
It throws exceptiopn:
Exception in thread "Ruby-0-Thread-54@[main]>worker0: :1" java.lang.NullPointerException

7

@zakkhan Can you try the below date filter:

  date {
    match => [ "datetime", "ISO8601" ]
  }

Reference:
https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html

ISO8601
ISO8601.PNG918×128 11.2 KB

8

Thanks but still not working.

9

Known issue, see https://github.com/logstash-plugins/logstash-filter-date/issues/95 for workaround.

10

Is this the right way to access DB column i.e. [DateTime] in filter section.

date{
match => ["DateTime", "YYYY-MM-dd HH:mm:ss.SSS"]
target => "@timestamp"
}

11

Is this the right way to access DB column i.e. [DateTime] in filter section.

Yes.

12

I am stuck. When i hard-code exact value of DateTime column in a temp field and use it instead of DateTime it works.

#DateTime = "2017-12-04 20:52:41.000"

mutate {
add_field => ["NewTimestamp","2017-12-04 20:52:41.000"]
}
date{
match => ["NewTimestamp", "YYYY-MM-dd HH:mm:ss.SSS"]
target => "@timestamp"
}

13

Did you read the GitHub issue I posted a link to?

14

Thanks. I was using wrong format of datetime conversion. My issue has been resolved now.

15

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