How to change datatype from String Logstash to ISODate MongoDB

Question

I have some logs that read by Filebeat, filter in Logstash and sent to MongoDB. Filebeat generate @timestamp field on logs, the log might look like this

{
    "_id" : ObjectId("622884e8ed814f1590000076"),
    "@timestamp" : "\"2022-03-09T10:43:46.000\"",
    "stream" : "stderr",
    "message" : "[2022-03-09T10:43:46.528612+00:00] testing.INFO: Message error [] []"
}

but the @timestamp field write as String not Date. I have to read the timestamp as ISODate on MongoDB. It might look like this

"@timestamp": ISODate("2022-03-10T01:43:46.000Z")

instead of

"@timestamp" : "\"2022-03-09T10:43:46.000\""

Any suggestion how to change the datatype from string into date?

UPDATE Trying to using match on date filter.

date {
       match => [ "@timestamp", "MMM dd yyyy HH:mm:ss",  "MMM  d yyyy HH:mm:ss", "ISO8601" ]
  } 
}

on mongo the datatype still not change. I also tried to make another field with current date (logstash_processed_at) with this

ruby {
        code => "event.set('logstash_processed_at', Time.now());"
        }

And using the date match too, but on the mongo the datatype still string

R2D2 · Answer 1 · 2022-03-11T08:55:08.030

0

Here is how to convert the timestamps already in mongoDB:

db.collection.update({},
 [
  {
   $addFields: {
    "@timestamp": {
      $dateFromString: {
         dateString: {
           $substr: [
            "$@timestamp",
            1,
            23
           ]
         }
       }
     }
   }
  }
 ],
 {
   multi: true
 })

playground

But you need to fix the injestion and use the logstash filter in order to convert the date in string to date in ISODate():

filter {   
  date {
       match => [ "logdate", "MMM dd yyyy HH:mm:ss",  "MMM  d yyyy HH:mm:ss", "ISO8601" ]
  } 
}

Also check here the monggodb output plugin:

isodate Value type is boolean Default value is false If true, store the @timestamp field in MongoDB as an ISODate type instead of an ISO8601 string.

edited Mar 11 '22 at 08:55

answered Mar 11 '22 at 08:38

R2D2

9,410
2
12
28

Hi, thankyou for your suggestion. I tried to using your way but on mongo the datatype still the same. It still shows as string not ISODate() – Dhody Rahmad Hidayat Mar 11 '22 at 09:47
Aah forgot to mention the update/aggregation solution require mongodb version >=4.2 , what is your version? – R2D2 Mar 11 '22 at 10:05
check this ticket here it is detailed how to update field from another field in different versions also the old fasion way via JS for very earlier versions: https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field – R2D2 Mar 11 '22 at 10:12
Im running on docker with docker.elastic.co/logstash/logstash:7.15 – Dhody Rahmad Hidayat Mar 11 '22 at 11:05
Maybe I'm not clear enough, I want to sent the logs from logstash to mongo but with ISODate() data type in mongo. Your query pretty works for documents inside the collection but instead of running the query to change whats inside collection, I want to sent the logs and mongo read the timestamp as ISODate() data type not string. Is it possible? I'm running mongo 4.2.17 ver. – Dhody Rahmad Hidayat Mar 11 '22 at 11:11

score 0 · Accepted Answer · edited Mar 11 '22 at 15:48

0

Instead of changing the filter it solved by adding new field on output mongodb at logstash config. the field is isodate => true.

output {
  mongodb {
    ...
    isodate => true
  }
}

This field change the @timestamp field from string to ISODate() format when it sent to mongodb. If you are looking for how changing the timestamp into ISODate() in mongodb, @R2D2 has the answer up there.

edited Mar 11 '22 at 15:48

Dharman

30,962
25
85
135

answered Mar 11 '22 at 15:42

Dhody Rahmad Hidayat

417
6
19

How to change datatype from String Logstash to ISODate MongoDB

2 Answers2