Expand json string into searchable fields

[murad]

Hi
Kinda a new to splunk . Sending data to splunk via HEC. Its a DTO which contains various fields, one of them being requestBody which is a string and it contains the JSON Payload my end point is receiving. When viewing the log event within splunk, the requestBody stays as string. I was hoping that it could be expanded so that the json fields could be searchable. 

As you can see, when i click on "body", the whole line is selected. I am hoping for , for example, "RYVBNQ" to be individually selectable so that i can do searches against that. 

[dtburrows3]

Looks like the HEC event is sending the "Request.body" field as a string literal and splunks KV_MODE=json (or INDEXED_EXTRACTION=json) is extracting it that way. 

It should be possible to extract it from there still but if you want it extracted at search time you may need to either adjust the source sending the data to send "Request.body" as a valid json array or set up a calculated field in props.conf to get the desired fields extracted (regex is also an option using props/transforms)

Here is some SPL of simulated data to give an example of what I think is going on and how Splunk is extracting the fields.

| makeresults
    | eval
        _raw="{\"ParentId\": \"\", \"Request\": {\"type\": \"RequestLogDTO\", \"body\": \"[ { \\\"recordLocator\\\": \\\"RYVBNQ\\\" } ]\", \"hostname\": \"IT-SALI\" }",
        example="Example 1: JSON Payload sent with 'Request.body{}' as a string wrapped in quotes"
    | spath input=_raw
    | append
        [
            | makeresults
                | eval
                    _raw="{\"ParentId\": \"\", \"Request\": {\"type\": \"RequestLogDTO\", \"body\": [ { \"recordLocator\": \"RYVBNQ\" } ], \"hostname\": \"IT-SALI\" }",
                    example="Example 2: JSON Payload sent with 'Request.body{}' as a json array (no quotes)"
                ``` The spath below would better represent how splunk would parse it at search time using KV_Mode=json if the array wasn't wrapped in double quotes ```
                | spath input=_raw
            ]
    | fields - _time
    | fields + example, _raw, "Request.body", "Request.body{}.recordLocator"

Output looks something like this.

It is also possible to still get those fields in your search pipeline if that is the route you want to go.

| makeresults
    | fields _ time
    | eval
        _raw="{\"ParentId\": \"\", \"Request\": {\"type\": \"RequestLogDTO\", \"body\": \"[ { \\\"recordLocator\\\": \\\"RYVBNQ\\\", \\\"depStartDate\\\": \\\"2023-12-14T14:00:19.671Z\\\", \\\"depEndDate\\\": \\\"2023-12-15T09:20:19.671Z\\\" } ]\", \"hostname\": \"IT-SALI\" }",
        example="Example 1: JSON Payload sent with 'Request.body{}' as a string wrapped in quotes"
    | spath input=_raw
    
    ``` This spath command should extract fields in "Request.body" fully and will be available with fieldnames formatted with a leading "{}" since it is an array ```
    | spath input=Request.body
    
    | fields + _raw, Request.body, "{}.*"

 
Snapshot of the expected output

From here you can rename the funky "{}.*" fields by doing the SPL

| rename "{}.*" as *

[yuanliu]

Note that Request.body is an array, which is flattened as multivalue.  This means that the any field inside Request.body is also multivalued.  The code should handle this.  The most common method is to add mvexpand against the array.

| spath input=Request.body path={}
| mvexpand {}
| spath input={}

Using the same emulation @dtburrows3 provides, the output is

ParentId	Request.body	Requet.hostname	Request.type	depEndDate	depStartData	recordLocator	{}
	[ { "recordLocator": "RYVBNQ", "depStartDate": "2023-12-14T14:00:19.671Z", "depEndDate": "2023-12-15T09:20:19.671Z" } ]	IT-SALI	RequestLogDTO	2023-12-15T09:20:19.671Z	2023-12-14T14:00:19.671Z	RYVBNQ	{ "recordLocator": "RYVBNQ", "depStartDate": "2023-12-14T14:00:19.671Z", "depEndDate": "2023-12-15T09:20:19.671Z" }

[inventsekar]

one more questions pls...

are you able to use "spath" splunk command to view each json field separately?

https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Spath

if yes, then, you can use regular expressions (rex) to search portion of the field u r looking to search.