parse Mongodb logs with syslog-ng

Question

I'm receiving a lot of Mongodb logs with my Syslog-ng. below is the sample of logs parsed and stored like this:

2016-10-18 19:01:08 f:local1.p:info h:10.133.126.81 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:02.439+0330 I COMMAND  [conn71796] command CLM.TroubleTicket command: find { find: "TroubleTicket", filter: { $and: [ { troubleTicket.serviceCode: "8118415922" } ] }, projection: { troubleTicket.referenceNumber: 1, troubleTicket.ticketGenerationDate: 1, troubleTicket.ticketCreatedDate: 1, troubleTicket.currentStatus: 1, troubleTicket.currentStatusReason: 1, troubleTicket.thirdPartyIncidentNumber: 1, troubleTicket.troubleTicketCatId: 1, troubleTicket.troubleTicketSubCatId: 1, troubleTicket.troubleTicketSubSubCatId: 1, troubleTicket.serviceCode: 1, troubleTicket.lastUpdateDate: 1, $sortKey: { $meta: "sortKey" } }, sort: { troubleTicket.ticketCreatedDate: -1 }, ntoreturn: 5, shardVersion: [ Timestamp 232000|1, ObjectId('578fb3a6e0f9dacf6705e34c') ] } planSummary: IXSCAN { troubleTicket.serviceCode: 1.0 }, IXSCAN { troubleTicket.serviceCode: 1.0 } cursorid:85032809863 keysExamined:97798 docsExamined:97798 hasSortStage:1 keyUpdates:0 writeConflicts:0 numYields:764 nreturned:5 reslen:2354 locks:{ Global: { acquireCount: { r: 1530 } }, Database: { acquireCount: { r: 765 } }, Collection: { acquireCount: { r: 765 } } } protocol:op_command 572ms
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.226+0330 I SHARDING [conn6447] request split points lookup for chunk CLM.ActionLevelDetails { : MinKey } -->> { : MaxKey }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.229+0330 W SHARDING [conn6447] possible low cardinality key detected in CLM.ActionLevelDetails - key is { actionLevelDetails.activityType: "CNFRMREG" }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.229+0330 W SHARDING [conn6447] possible low cardinality key detected in CLM.ActionLevelDetails - key is { actionLevelDetails.activityType: "DOCSUPLOAD" }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.234+0330 I SHARDING [conn6447] request split points lookup for chunk CLM.ActionLevelDetails { : MinKey } -->> { : MaxKey }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.237+0330 W SHARDING [conn6447] possible low cardinality key detected in CLM.ActionLevelDetails - key is { actionLevelDetails.activityType: "CNFRMREG" }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.237+0330 W SHARDING [conn6447] possible low cardinality key detected in CLM.ActionLevelDetails - key is { actionLevelDetails.activityType: "DOCSUPLOAD" }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.350+0330 I SHARDING [conn6447] request split points lookup for chunk CLM.ActionLevelDetails { : MinKey } -->> { : MaxKey }
2016-10-18 19:01:17 f:local1.p:info h:10.133.126.80 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:10.353+0330 W SHARDING [conn6447] possible low cardinality key detected in CLM.ActionLevelDetails - key is { actionLevelDetails.activityType: "CNFRMREG" }
2016-10-18 19:01:18 f:local1.p:info h:10.133.126.81 prog:sharmongo-log m:sharmongo-log 2016-10-18T19:01:16.762+0330 I ACCESS   [conn6012] Successfully authenticated as principal dba_admin on admin

note that Mongodb log message contains JSON format as you can see in logs. The config of syslog-ng for these logs are as below:

source s_all {
                udp(ip("0.0.0.0") port(514));
                tcp(ip("0.0.0.0") port(514) keep-alive(no) max-connections(1000));
};


destination d_clm_mongodb {
   file("/storage/sensage/incoming/mtn/syslog-ng/clm_mongodb/clm_mongodb.log"
   template("$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC f:$FACILITY.p:$PRIORITY h:$HOST_FROM prog:$PROGRAM m:$MSG\n")
   template_escape(no) );
};

filter f_clm_mongodb          { program("sharmongo-log"); };

log { source(s_all); filter(f_clm_mongodb);       destination(d_clm_mongodb);       flags(final); };

I need to parse these logs to CSV format (comma separated) meaning that event JSON part should be seperated with comma. I searched a lot about this issue. I need to now is there a capability in syslog-ng that parse the JSON logs (Smaples) and store with CSV format?

Note: The mongodb log format is as following link: https://github.com/rueckstiess/mongodb-log-spec

Answer 1

that's a tricky one. The problem imho is, that there are JSON objects intermixed with plain text fields. I think you have the following options (note that you'll need a recent syslog-ng version to use the json and the k-v parsers, I'd go for version 3.8):

• If you can, configure mongodb to log into pure json, and parse that with syslog-ng's json-parser. (Don't know if mongodb can do this.)

• You could build a pattern database to cover the individual messages, but that can take lot of time

• But the most likely option would be to use a combination of syslog-ng parsers. Namely, try the following:

  • use a csv-parser to split the message into two columns at the first { character
  • parse the first column using a key-value parser (the colon is a separator in this part of the message)
  • use a json-parser to parse the second part of the message (since some messages have multiple json parts, you might have to add another csv+json combo here) These parsers will create name-value pairs of the parsed values, and you can use a template or a template function to output them as you need (for example, using the format-welf template function).

• Or now that I think of it, if you do not need the JSON structure (only the flat names+values), then you can try to simply use a rewrite rule to remove the {} characters from the messages, and use a key-value parser.

• If the above option does not work, you can write a custom parser in python and process the messages there.

HTH.

Please let me know if you succeed.