Parsing json inside json

[eirik]

I'm new to logstash and wanted to test using it reading loglines from IBM Cloud and struggling with parsing this log record using logstash. The log line is a json object, and inside this one of the fields starts with plain text and then contains a new json object.

I only want to keep log lines where the msg field inside the json document in the message field contains the text "Slow query"

I want to keep fields _ip and _ipremote from the outer json document and then parse the message field by removing the plain text and return the jsonobject together with the 2 values from the _ip and _ipremote values. all these values should be inserted into elastic.

Anyone out there that can give me any pointers

{
    "_host": "ibm-cloud-databases-prod",
    "_label": {
        "database": "mongodb",
        "member": "m-0",
        "crn": "crn:v1:bluemix:public:databases-for-mongodb",
        "region": "eu-de"
    },
    "_logtype": "json",
    "_file": "crn:v1:bluemix:public:databases-for-mongodb",
    "_ts": 1678669328787,
    "_platform": "ibm-cloud-databases-prod",
    "_app": "crn:v1:bluemix:public:databases-for-mongodb",
    "_ip": "172.33.226.32",
    "_id": "1588254284607938573",
    "_ipremote": "126.19.43.43",
    "message": "2023-03-13T01:02:07.926431297Z stdout F {\"t\":{\"$date\":\"2023-03-13T01:02:07.926+00:00\"},\"s\":\"I\",  \"c\":\"COMMAND\",  \"id\":51803,   \"ctx\":\"conn49\",\"msg\":\"Slow query\",\"attr\":{\"type\":\"command\",\"ns\":\"local.oplog.rs\",\"command\":{\"find\":\"oplog.rs\",\"filter\":{\"ts\":{\"gte\":{\"$timestamp\":{\"t\":1678665727,\"i\":0}}}},\"lsid\":{\"id\":{\"$uuid\":\"34gdg-12c5-4344335-b49a-sdfe34ce4757\"}},\"$clusterTime\":{\"clusterTime\":{\"$timestamp\":{\"t\":1678669321,\"i\":1}},\"signature\":{\"hash\":{\"$binary\":{\"base64\":\"AAAAAAAAAAAAAAAAAAAAAAAAAAA=\",\"subType\":\"0\"}},\"keyId\":0}},\"$db\":\"local\",\"$readPreference\":{\"mode\":\"primaryPreferred\"}},\"planSummary\":\"COLLSCAN\",\"keysExamined\":0,\"docsExamined\":1152813,\"cursorExhausted\":true,\"numYields\":1152,\"nreturned\":0,\"queryHash\":\"D06EDDC0\",\"planCacheKey\":\"D06EDDC0\",\"reslen\":228,\"locks\":{\"ReplicationStateTransition\":{\"acquireCount\":{\"w\":1153}},\"Global\":{\"acquireCount\":{\"r\":1153}},\"Database\":{\"acquireCount\":{\"r\":1153}},\"Mutex\":{\"acquireCount\":{\"r\":1}},\"oplog\":{\"acquireCount\":{\"r\":1153}}},\"storage\":{\"data\":{\"bytesRead\":209816454,\"timeReadingMicros\":87782}},\"protocol\":\"op_msg\",\"durationMillis\":845}}",
    "saveServiceCopy": true,
    "logSourceCRN": "crn:v1:bluemix:public:databases-for-mongodb",
    "_mezmo_line_size": 344
}

[Badger]

You could try

    json { source => "message" }
    prune { whitelist_names => [ "message", "_ip", "_ipremote" ] }
    dissect { mapping => { "message" => "%{[@metadata][ts]} %{} %{} %{[@metadata][restOfline]}" } }
    date { match => [ "[@metadata][ts]", "ISO8601" ] }
    json { source => "[@metadata][restOfline]" remove_field => [ "message" ] }
    if [msg] !~ /Slow query/ { drop {} }

That assumes that the [message] field in the event starts off containing the entire pretty-printed JSON object.

[eirik]

Thanks very much for your help