Querying log events based on field values nested inside a escaped raw JSON property

[btsr]

Hi, I need some help with querying log events based on field values nested inside a escaped raw JSON object property. Below screenshot represents the complete JSON log event in "Show syntax highlighted" mode. I need to filter events based on properties in the parent JSON object and also combine field values from msg.object which is also a proper JSON object.

I tried rex, spath but couldn't filter the events the way I need. Any help is appreciated. Thanks.

I am looking for a query to filter events matching the highlighted fields in the Splunk event log screenshot. This is for a dashboard with dropdowns for app, clientName, requestType, and state fields. I should be able to filter the log events based on dropdown selection.

index=x05_dev app=mock-app msg.detail{}.value=value1 | search msg.object.headers.requestType="basic" | search msg.object.body.client.clientName="XyzClient" | search msg.object.body.order.details[*].address.state="MN"

Sample Splunk Log Event

[yeahnah]

Hi @btsr 

Can you provide the raw JSON as a code sample as this makes it easier to provide a working example that shows you what you need to do using SPL.

On a side note, you should look at cleaning this data up before it is ingested - basically object is showing the HTML code for a double quote - " = &#34.

[btsr]

I think we need to get the msg.object and replace all HTML codes to double quotes on the fly to make a valid JSON before querying further. 

{
  "app": "mock-app",
  "sessionId": "71cde99f-faa9-47df-99d4-97b81b39275b",
  "msg": {
    "event": "response",
    "status": "success",
    "details": [
      {
        "key": "key1",
        "value": "value1"
      }
    ],
    "method": "post",
    "object":{
      \u0026#34;headers\u0026#34;: {
        \u0026#34;content-length\u0026#34;: \u0026#34;225\u0026#34;,
        \u0026#34;requestType\u0026#34;: \u0026#34;basic\u0026#34;
      },
      \u0026#34;body\u0026#34;: {
        \\\u0026#34;client\\\u0026#34;: {
            \\\u0026#34;clientName\\\u0026#34;: \\\u0026#34;XyzClient\\\u0026#34;
        },
        \\\u0026#34;order\\\u0026#34;: {
            \\\u0026#34;number\\\u0026#34;: \\\u0026#34;551270009\\\u0026#34;,
            \\\u0026#34;details\\\u0026#34;: [
            {
                \\\u0026#34;item\\\u0026#34;: \\\u0026#34;product\\\u0026#34;,
                \\\u0026#34;address\\\u0026#34;: {
                  \\\u0026#34;street\\\u0026#34;: \\\u0026#34;Main St\\\u0026#34;,
                  \\\u0026#34;zip\\\u0026#34;: \\\u0026#34;12345\\\u0026#34;,
                  \\\u0026#34;state\\\u0026#34;: \\\u0026#34;MN\u0026#34;,
                \\\u0026#34;city\\\u0026#34;: \\\u0026#34;XyzCity\\\u0026#34;
            }
          }
        ]
      }
    }  
    }
  }
}

[btsr]

Thanks @yeahnah, the msg.object field is ingested with HTML code like shown in the screenshot by the Splunk team to allow different teams to use it for different free form JSON format. If I click on "Show as Raw Text" in Splunk, it is replacing all ampersand symbol with \u0026

[yeahnah]

Hi @btsr 

Try adding this sed character replacement using the rex command, which should normalise the escaped code in the msg.object back to double quotes expected for JSON formats, then spath will pull the kv fields out of the JSON event again

... your search ...
| rex mode=sed "s/(\\\)*u0026#34;/\"/g"
| spath msg.object
| ... more SPL, as needed ...

 Hope it helps