topic Re: How to extract nested JSON and display JsonObject in a table? in Splunk Search

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

Dhanapathi — Fri, 28 Jun 2019 16:23:03 GMT

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.

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

kamlesh_vaghela — Mon, 01 Jul 2019 04:58:50 GMT

@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?

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

Dhanapathi — Mon, 01 Jul 2019 15:55:25 GMT

{
    "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

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

memarshall63 — Mon, 01 Jul 2019 16:40:24 GMT

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

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

Dhanapathi — Mon, 01 Jul 2019 19:30:55 GMT

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

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

jawaharas — Tue, 02 Jul 2019 06:54:51 GMT

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

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

kamlesh_vaghela — Tue, 02 Jul 2019 08:33:29 GMT

@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.

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

Dhanapathi — Wed, 03 Jul 2019 18:10:24 GMT

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

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

Dhanapathi — Wed, 03 Jul 2019 18:14:56 GMT

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?