Need help to send Datetime in BSON ISODATE to MongoDB?
Hello, I have integration working fine between syslog-ng v3.38 and MongoDB v6. I am working to enable MongoDB Time-Series Collections on a testing DB for the purpose of having better efficiency and improved queries over time. I created the DB and collection, tested it by Mongo Compass client, then I tried to send the data from syslog-ng but it didn't work. MongoDB is throwing the following error: "17:12:57. 483 | [2023-08-06T17:12:57.355549] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0" My understanding is that it is a must to send the time/date to MongoDB with Time Series enabled DB a BSON format Date. Any suggestions? *Below is the mongoDB destination config:* destination d_mongodb_ttl { mongodb( uri("mongodb://192.168.1.19:27017/syslog-ng_ttl") collection("logs") value-pairs( scope("selected-macros") exclude("SOURCEIP") exclude("TAGS") pair("datetime" "${S_ISODATE}") pair("privateip" "${privateip}") pair("publicip" "${publicip}") pair("publicportrange" "${publicportrange}") pair("portaction" "${AI}") pair("portstatus" "${AL}") pair("host" "${HOST}")) ); }; Appreciate your help
If you change your datetime to be compsed of the epoc and the milliseconds it should work. pair("datetime" "${S_UNIXTIME}${S_MSEC}") Evan On 2023-08-06 08:04, Ibrahim Al Mahfooz wrote:
Hello,
I have integration working fine between syslog-ng v3.38 and MongoDB v6. I am working to enable MongoDB Time-Series Collections on a testing DB for the purpose of having better efficiency and improved queries over time.
I created the DB and collection, tested it by Mongo Compass client, then I tried to send the data from syslog-ng but it didn't work. MongoDB is throwing the following error: "17:12:57. 483 | [2023-08-06T17:12:57.355549] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0"
My understanding is that it is a must to send the time/date to MongoDB with Time Series enabled DB a BSON format Date. Any suggestions?
_Below is the mongoDB destination config:_
destination d_mongodb_ttl { mongodb( uri("mongodb://192.168.1.19:27017/syslog-ng_ttl <http://192.168.1.19:27017/syslog-ng_ttl>") collection("logs") value-pairs( scope("selected-macros") exclude("SOURCEIP") exclude("TAGS") pair("datetime" "${S_ISODATE}") pair("privateip" "${privateip}") pair("publicip" "${publicip}") pair("publicportrange" "${publicportrange}") pair("portaction" "${AI}") pair("portstatus" "${AL}") pair("host" "${HOST}")) ); };
Appreciate your help
Hello Evan, Thanks for your response,
If you change your datetime to be compsed of the epoc and the milliseconds it should work.
pair("datetime" "${S_UNIXTIME}${S_MSEC}")
I made the change but didn't work, got same log error, also you can see the datetime is UNIX+MSEC is reflected in the datetime pair but [2023-08-06T21:38:53.717614] Outgoing message to MongoDB destination; message='datetime=1691347133717 host=10.36.0.6 portaction=initial portstatus=assigned privateip=10.34.102.175 publicip=98.75.22.22 publicportrange=9048~10047 ', driver='d_mongodb_ttl#0' [2023-08-06T21:38:53.718564] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0'
From what I understand from MongoDB, BSON format is mandatory, and MongoDB expectation is this binary format only, if syslog-ng is not capable of sending such format, most probably things will not work out.
Do you have a working MongoDB Time-Series setup with syslog-ng before? Best regards On Sun, 6 Aug 2023 at 18:04, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Hello,
I have integration working fine between syslog-ng v3.38 and MongoDB v6. I am working to enable MongoDB Time-Series Collections on a testing DB for the purpose of having better efficiency and improved queries over time.
I created the DB and collection, tested it by Mongo Compass client, then I tried to send the data from syslog-ng but it didn't work. MongoDB is throwing the following error: "17:12:57. 483 | [2023-08-06T17:12:57.355549] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0"
My understanding is that it is a must to send the time/date to MongoDB with Time Series enabled DB a BSON format Date. Any suggestions?
*Below is the mongoDB destination config:*
destination d_mongodb_ttl { mongodb( uri("mongodb://192.168.1.19:27017/syslog-ng_ttl") collection("logs") value-pairs( scope("selected-macros") exclude("SOURCEIP") exclude("TAGS") pair("datetime" "${S_ISODATE}") pair("privateip" "${privateip}") pair("publicip" "${publicip}") pair("publicportrange" "${publicportrange}") pair("portaction" "${AI}") pair("portstatus" "${AL}") pair("host" "${HOST}")) ); };
Appreciate your help
This documentation https://www.mongodb.com/docs/manual/reference/bson-types/#date shows that mongodb needs a 64 bit integer value to George l represent datetime, which is the number of milliseconds since the epoch. You will need to tell syslog-ng to encode this value as an integer. pair("datetime" int64("${S_UNIXTIME}${S_MSEC}")) Please note the int64 type hint On Sun, Aug 6, 2023, 20:52 Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Hello Evan,
Thanks for your response,
If you change your datetime to be compsed of the epoc and the milliseconds it should work.
pair("datetime" "${S_UNIXTIME}${S_MSEC}")
I made the change but didn't work, got same log error, also you can see the datetime is UNIX+MSEC is reflected in the datetime pair but [2023-08-06T21:38:53.717614] Outgoing message to MongoDB destination; message='datetime=1691347133717 host=10.36.0.6 portaction=initial portstatus=assigned privateip=10.34.102.175 publicip=98.75.22.22 publicportrange=9048~10047 ', driver='d_mongodb_ttl#0' [2023-08-06T21:38:53.718564] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0'
From what I understand from MongoDB, BSON format is mandatory, and MongoDB expectation is this binary format only, if syslog-ng is not capable of sending such format, most probably things will not work out.
Do you have a working MongoDB Time-Series setup with syslog-ng before?
Best regards
On Sun, 6 Aug 2023 at 18:04, Ibrahim Al Mahfooz < ibrahim.nezar@sevennet.net> wrote:
Hello,
I have integration working fine between syslog-ng v3.38 and MongoDB v6. I am working to enable MongoDB Time-Series Collections on a testing DB for the purpose of having better efficiency and improved queries over time.
I created the DB and collection, tested it by Mongo Compass client, then I tried to send the data from syslog-ng but it didn't work. MongoDB is throwing the following error: "17:12:57. 483 | [2023-08-06T17:12:57.355549] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0"
My understanding is that it is a must to send the time/date to MongoDB with Time Series enabled DB a BSON format Date. Any suggestions?
*Below is the mongoDB destination config:*
destination d_mongodb_ttl { mongodb( uri("mongodb://192.168.1.19:27017/syslog-ng_ttl") collection("logs") value-pairs( scope("selected-macros") exclude("SOURCEIP") exclude("TAGS") pair("datetime" "${S_ISODATE}") pair("privateip" "${privateip}") pair("publicip" "${publicip}") pair("publicportrange" "${publicportrange}") pair("portaction" "${AI}") pair("portstatus" "${AL}") pair("host" "${HOST}")) ); };
Appreciate your help
______________________________________________________________________________ Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng FAQ: http://www.balabit.com/wiki/syslog-ng-faq
You will need to tell syslog-ng to encode this value as an integer. pair("datetime" int64("${S_UNIXTIME}${S_MSEC}")) Please note the int64 type hint
I tried it and it also didn't work.
I figured out the solution by using the datetime function, apparently when working with MongoDB time-series or TTL, it is mandatory that the datetime pair value must be sent of type datetime not a string, int or other types otherwise MongoDB won't accept it. Here is the pair structure worked for me: pair("createdAt" datetime($S_UNIXTIME)) Thanks. On Sun, 6 Aug 2023 at 21:51, Ibrahim Al Mahfooz <ibrahim.nezar@sevennet.net> wrote:
Hello Evan,
Thanks for your response,
If you change your datetime to be compsed of the epoc and the milliseconds it should work.
pair("datetime" "${S_UNIXTIME}${S_MSEC}")
I made the change but didn't work, got same log error, also you can see the datetime is UNIX+MSEC is reflected in the datetime pair but [2023-08-06T21:38:53.717614] Outgoing message to MongoDB destination; message='datetime=1691347133717 host=10.36.0.6 portaction=initial portstatus=assigned privateip=10.34.102.175 publicip=98.75.22.22 publicportrange=9048~10047 ', driver='d_mongodb_ttl#0' [2023-08-06T21:38:53.718564] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0'
From what I understand from MongoDB, BSON format is mandatory, and MongoDB expectation is this binary format only, if syslog-ng is not capable of sending such format, most probably things will not work out.
Do you have a working MongoDB Time-Series setup with syslog-ng before?
Best regards
On Sun, 6 Aug 2023 at 18:04, Ibrahim Al Mahfooz < ibrahim.nezar@sevennet.net> wrote:
Hello,
I have integration working fine between syslog-ng v3.38 and MongoDB v6. I am working to enable MongoDB Time-Series Collections on a testing DB for the purpose of having better efficiency and improved queries over time.
I created the DB and collection, tested it by Mongo Compass client, then I tried to send the data from syslog-ng but it didn't work. MongoDB is throwing the following error: "17:12:57. 483 | [2023-08-06T17:12:57.355549] Failed to insert into MongoDB; time_reopen='10', reason='\'datetime\' must be present and contain a valid BSON UTC datetime value', driver='d_mongodb_ttl#0"
My understanding is that it is a must to send the time/date to MongoDB with Time Series enabled DB a BSON format Date. Any suggestions?
*Below is the mongoDB destination config:*
destination d_mongodb_ttl { mongodb( uri("mongodb://192.168.1.19:27017/syslog-ng_ttl") collection("logs") value-pairs( scope("selected-macros") exclude("SOURCEIP") exclude("TAGS") pair("datetime" "${S_ISODATE}") pair("privateip" "${privateip}") pair("publicip" "${publicip}") pair("publicportrange" "${publicportrange}") pair("portaction" "${AI}") pair("portstatus" "${AL}") pair("host" "${HOST}")) ); };
Appreciate your help
-- Ibrahim Abdulatif IT and EPC Core Signaling Manager Sevennet Layers Co., LTD. +964 (0) 53 511 5514 <+964+(0)+53+511+5514> | +964 (0) 770 471 5514 <+964+(0)+770+471+5514> www.sevennet.net 2nd Floor, Faruk Investment Group, Salim Street, Sulaymaniyah, Iraq
participants (3)
-
Balazs Scheidler
-
Evan Rempel
-
Ibrahim Al Mahfooz