Splunk Search

How to extract nested JSON and display JsonObject in a table?

Dhanapathi
New Member

My sample event looks like below:

{
    "thread": "http-nio-8085-exec-1",
    "level": "INFO",
    "loggerName": "INSTRUMENTATION_TRACING",
    "message": {
        "eventId": "68b3c5d3-02e5-4c33-af41-756b43fc4311",
        "signature": "public ResponseObj someMethod(RequestObj)",
        "duration": 1019,
        "request": [{"#my nested request Json goes here": "" } ],
        "response": {"#my nested response Json goes here": ""},
    "endOfBatch": false,
    "loggerFqcn": "org.apache.logging.log4j.spi.AbstractLogger",
    "instant": {
        "epochSecond": 1561664957,
        "nanoOfSecond": 437000000
    },
    "threadId": 26,
    "threadPriority": 5
}

I would like to display in a table like below

|eventId | signature | duration | request | response

Issue: I was able to extract each element in a nested JSON but the cloud is not able to aggregate 'message.request' as one JSON String
Tried below :

index=sample loggerName="INSTRUMENTATION_TRACING" | spath | rename message.eventId as eventId, message.signature as signature message.duration as duration , message.request as request, message.response as response | table eventId, signature, duration, request, response

spath extracts all nested elements as expected. I could not see "message.request" or "message.response" in the extracted fields.

I only want to extract one level (i.e I want message.request and message.response elements as Json string to display in a table)

Is it possible? If so, can someone throw me some ideas, please.

0 Karma

jawaharas
Motivator

Below SPL works for 'request' field as the field is a 'list' in the JSON input.

|<base_search>
| spath output=request path=request{}
| table eventId, signature, duration, request
0 Karma

Dhanapathi
New Member

Sorry.., I need to display both request and response

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Dhanapathi

Can you please share sample JSON for "request": [{"#my nested request Json goes here": "" } ],
"response": {"#my nested response Json goes here": ""},
and expected result from it?

0 Karma

memarshall63
Communicator

Is that the formatted JSON (i.e. coming from Splunk display) or does your event have the whitespaces and newlines in it?

0 Karma

Dhanapathi
New Member

I will have whitespaces and newlines in my Splunk log event. The Json content of request and response changes based on the operation

0 Karma

Dhanapathi
New Member
{
    "eventId": "1065f4a2-d61d-438d-9862-6db0c41b5000",
    "metrics": {
        "NAME": "pilot-vehicle-service",
        "VERSION": "1.0",
        "ENVIRONMENT": "DEV",
        "INSTANCE_ID": "11",
        "CORRELATION_ID": "3259eef8-afab-4cf1-a8c3-20ed9682aefe",
        "LOCAL_ADDRESS": "127.0.0.1:8080 ",
        "REMOTE_ADDRESS": "127.0.0.1",
        "URI": "[POST]/pilot-vehicle-service/0.0/vehicle/preference"
    },
    "signature": "public me.dhana.poc.vehicle.domain.Vehicle me.dhana.poc.vehicle.service.impl.DefaultVehicleQueryService.getPreference(me.dhana.poc.vehicle.domain.Category)",
    "duration": 343,
    "request": [
        {
            "segment": "compact",
            "type": "suv"
        }
    ],
    "response": {
        "id": "5",
        "manufacturer": "Honda",
        "year": 2017,
        "model": "Pilot",
        "price": 13300
    }
}

Please find the sample above

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@Dhanapathi

I assume that this event from the search bar.

Can you please execute below searches?

index=sample loggerName="INSTRUMENTATION_TRACING" | spath 
| rename message.* as * | table  eventId, signature, duration, request{}*, response.*

Sample Search:

| makeresults 
| eval _raw="{
    \"thread\": \"http-nio-8085-exec-1\",
    \"level\": \"INFO\",
    \"loggerName\": \"INSTRUMENTATION_TRACING\",
    \"message\": {
        \"eventId\": \"1065f4a2-d61d-438d-9862-6db0c41b5000\",
        \"metrics\": {
            \"NAME\": \"pilot-vehicle-service\",
            \"VERSION\": \"1.0\",
            \"ENVIRONMENT\": \"DEV\",
            \"INSTANCE_ID\": \"11\",
            \"CORRELATION_ID\": \"3259eef8-afab-4cf1-a8c3-20ed9682aefe\",
            \"LOCAL_ADDRESS\": \"127.0.0.1:8080 \",
            \"REMOTE_ADDRESS\": \"127.0.0.1\",
            \"URI\": \"[POST]/pilot-vehicle-service/0.0/vehicle/preference\"
        },
        \"signature\": \"public me.dhana.poc.vehicle.domain.Vehicle me.dhana.poc.vehicle.service.impl.DefaultVehicleQueryService.getPreference(me.dhana.poc.vehicle.domain.Category)\",
        \"duration\": 343,
        \"request\": [{
            \"segment\": \"compact\",
            \"type\": \"suv\"
        },{
            \"segment\": \"compact1\",
            \"type\": \"suv1\"
        }],
        \"response\": {
            \"id\": \"5\",
            \"manufacturer\": \"Honda\",
            \"year\": 2017,
            \"model\": \"Pilot\",
            \"price\": 13300
        }
    }
}" 
| spath
| rename message.* as * | table  eventId, signature, duration, request{}*, response.*

Note: in your JSON request is array type of object So multivalued field could found. See in sample search.

0 Karma

Dhanapathi
New Member

I tried your options, but the request/response is not displayed as one single column, each json element is displayed in its own column.

Is there a way that i can convert the extracted Json back to String and display(request/response) in one single Column?

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...