topic Re: extract fields in json format in Splunk Search

How to extract fields in json format?

vineela — Fri, 06 Jan 2023 14:00:53 GMT

i need to extract fields which are in json format i have been trying using spath command for extracting the following fields which are under log. But not able to fetch it. I am failing somewhere.
Here is the example of my data:

{"log":"[18:15:21.888] [INFO ] [] [c.c.n.t.e.i.T.ServiceCalloutEventData] [akka://MmsAuCluster/user/$b/workMonitorActor/$M+c] - channel=\"AutoNotification\", productVersion=\"2.3.3-0-1-eb5b8cadd\", apiVersion=\"V1\", uuid=\"0b8549ff-1f14-4fd5-99c5-b3f2240d7da8\", eventDateTime=\"2023-01-06T07:15:21.888Z\", severity=\"INFO\", code=\"ServiceCalloutEventData\", component=\"web.client\", category=\"integrational-external\", serviceName=\"Consume Notification\", eventName=\"MANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUEST\", message=\"Schedule Job start, getNotification request\", entityType=\"MNDT\", externalSystem=\"SWIFTPAG\", start=\"1672989321888\", url=\"https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications\", swiftMessagePartnerBIC=\"RESTMP01\", messageIdentification=\"e1f24a3b8d9111edb3368d1476d87136\", subscriptionIdentification=\"29fbe070057811eca4fa68aa418f5c2a\" producer=com.clear2pay.na.mms.au.notification.batch.GetNotificationService \n","stream":"stdout","docker":{"container_id":"89efc58c0a343ee01daa2fcdeadb3b952599f0c142fb7041f95a9d6702fe49d2"},"kubernetes":{"container_name":"mms-au","namespace_name":"msaas-t4","pod_name":"mms-au-b-1-54b4589f89-g74lp","container_image":"pso.docker.internal.cba/mms-au:2.3.3-0-1-eb5b8cadd","container_image_id":"docker-pullable://pso.docker.internal.cba/mms-au@sha256:9d48d5af268d28708120ee3f69b576d371b5e603a0e0c925c7dba66058654819","pod_id":"b474ec16-fc9f-4b7a-9319-8302c0185f83","pod_ip":"100.64.87.219","host":"ip-10-3-197-177.ap-southeast-2.compute.internal","labels":{"app":"mms-au","dc":"b-1","pod-template-hash":"54b4589f89","release":"mms-au"},"master_url":"https://172.20.0.1:443/api","namespace_id":"48ee871a-7e60-45c4-b0f4-ee320a9512f5","namespace_labels":{"argocd.argoproj.io/instance":"appspaces","ci":"CM0953076","kubernetes.io/metadata.name":"msaas-t4","name":"msaas-t4","platform":"PSU","service_owner":"somersd","spg":"CBA_PAYMENTS_TEST_COORDINATION"}},"hostname":"ip-10-3-197-177.ap-southeast-2.compute.internal","host_ip":"10.3.197.177","cluster":"nonprod/pmn02"}

i need to extract few events which are under log.Can anyone help me on this.

Thanks in Advance

Re: extract fields in json format

yuanliu — Fri, 06 Jan 2023 08:13:06 GMT

I see no problem with spath if I just plug your sample. (In fact, if that's your raw event, you shouldn't need spath at all. Splunk should have already extracted all JSON nodes for you.) Do you mean that you want to extract key-value pair in the "log" field? You need extract (aka kv) command.

| rename _raw as temp, log as _raw | kv | rename temp as _raw ``` we are losing log by not renaming _raw back ```

Excluding all original JSON fields and _raw, the output from your sample would be

apiVersion

category

channel

code

component

entityType

eventDateTime

eventName

externalSystem

message

messageIdentification

producer

productVersion

serviceName

severity

start

subscriptionIdentification

swiftMessagePartnerBIC

url

uuid

integrational-external

AutoNotification

ServiceCalloutEventData

web.client

MNDT

2023-01-06T07:15:21.888Z

MANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUEST

SWIFTPAG

Schedule Job start, getNotification request

e1f24a3b8d9111edb3368d1476d87136

com.clear2pay.na.mms.au.notification.batch.GetNotificationService

2.3.3-0-1-eb5b8cadd

Consume Notification

INFO

1672989321888

29fbe070057811eca4fa68aa418f5c2a

RESTMP01

https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications

0b8549ff-1f14-4fd5-99c5-b3f2240d7da8

Here is an emulated test for you to play with and compare with your actual data.

| makeresults | fields - _time | eval _raw = "{\"log\":\"[18:15:21.888] [INFO ] [] [c.c.n.t.e.i.T.ServiceCalloutEventData] [akka://MmsAuCluster/user/$b/workMonitorActor/$M+c] - channel=\\\"AutoNotification\\\", productVersion=\\\"2.3.3-0-1-eb5b8cadd\\\", apiVersion=\\\"V1\\\", uuid=\\\"0b8549ff-1f14-4fd5-99c5-b3f2240d7da8\\\", eventDateTime=\\\"2023-01-06T07:15:21.888Z\\\", severity=\\\"INFO\\\", code=\\\"ServiceCalloutEventData\\\", component=\\\"web.client\\\", category=\\\"integrational-external\\\", serviceName=\\\"Consume Notification\\\", eventName=\\\"MANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUEST\\\", message=\\\"Schedule Job start, getNotification request\\\", entityType=\\\"MNDT\\\", externalSystem=\\\"SWIFTPAG\\\", start=\\\"1672989321888\\\", url=\\\"https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications\\\", swiftMessagePartnerBIC=\\\"RESTMP01\\\", messageIdentification=\\\"e1f24a3b8d9111edb3368d1476d87136\\\", subscriptionIdentification=\\\"29fbe070057811eca4fa68aa418f5c2a\\\" producer=com.clear2pay.na.mms.au.notification.batch.GetNotificationService \\n\",\"stream\":\"stdout\",\"docker\":{\"container_id\":\"89efc58c0a343ee01daa2fcdeadb3b952599f0c142fb7041f95a9d6702fe49d2\"},\"kubernetes\":{\"container_name\":\"mms-au\",\"namespace_name\":\"msaas-t4\",\"pod_name\":\"mms-au-b-1-54b4589f89-g74lp\",\"container_image\":\"pso.docker.internal.cba/mms-au:2.3.3-0-1-eb5b8cadd\",\"container_image_id\":\"docker-pullable://pso.docker.internal.cba/mms-au@sha256:9d48d5af268d28708120ee3f69b576d371b5e603a0e0c925c7dba66058654819\",\"pod_id\":\"b474ec16-fc9f-4b7a-9319-8302c0185f83\",\"pod_ip\":\"100.64.87.219\",\"host\":\"ip-10-3-197-177.ap-southeast-2.compute.internal\",\"labels\":{\"app\":\"mms-au\",\"dc\":\"b-1\",\"pod-template-hash\":\"54b4589f89\",\"release\":\"mms-au\"},\"master_url\":\"https://172.20.0.1:443/api\",\"namespace_id\":\"48ee871a-7e60-45c4-b0f4-ee320a9512f5\",\"namespace_labels\":{\"argocd.argoproj.io/instance\":\"appspaces\",\"ci\":\"CM0953076\",\"kubernetes.io/metadata.name\":\"msaas-t4\",\"name\":\"msaas-t4\",\"platform\":\"PSU\",\"service_owner\":\"somersd\",\"spg\":\"CBA_PAYMENTS_TEST_COORDINATION\"}},\"hostname\":\"ip-10-3-197-177.ap-southeast-2.compute.internal\",\"host_ip\":\"10.3.197.177\",\"cluster\":\"nonprod/pmn02\"}" | spath ``` data emulation above ```

Re: extract fields in json format

vineela — Fri, 06 Jan 2023 10:14:16 GMT

Hi Yuanliu,

Thanks for your reply.
May be ia m using in wrong way or not i am not sure..i am not able to fetch results using your command.

And i tried using makeresults but my requirement is to fetch details from log events...i mean key value pairs which are present under field log.Can you please help me on the same.

Re: extract fields in json format

yuanliu — Sat, 07 Jan 2023 03:00:39 GMT

Have you tried my code? That is to extract key-value pair in the field log.

Re: extract fields in json format

vineela — Sat, 07 Jan 2023 03:02:46 GMT

Yes i tried but not able to fetch results .i shared screenshot as well.

Re: extract fields in json format

yuanliu — Sat, 07 Jan 2023 03:08:54 GMT

The pairs are extracted; you can't see then because you are in "Fast mode". If you switch to "Smart mode" or "Verbose mode", you'll see them listed in the left. But you can also just table those columns, like

Re: extract fields in json format

vineela — Sat, 07 Jan 2023 04:28:26 GMT

Yes, i am able to see results now. But again one more issue i am facing i need to extract "ServiceCalloutEventData" field - which is highlighted under,it is not extracted as key value pair from log.

here is the sample log: log\":\"[18:15:21.888] [INFO ] [] [c.c.n.t.e.i.T.ServiceCalloutEventData] [akka://MmsAuCluster/user/$b/workMonitorActor/$M+c] - channel=\\\"AutoNotification\\\", productVersion=\\\"2.3.3-0-1-eb5b8cadd\\\", apiVersion=\\\"V1\\\", uuid=\\\"0b8549ff-1f14-4fd5-99c5-b3f2240d7da8\\\", eventDateTime=\\\"2023-01-06T07:15:21.888Z\\\", severity=\\\"INFO

Re: extract fields in json format

yuanliu — Sat, 07 Jan 2023 07:12:04 GMT

You won't get it from extract command because that part is not in any key-value pair that you can define. It is in the fourth bracket that come before all the key-value pairs. You'll need to first separate the two parts, then get the content of those brackets.

| eval log = split(log, " - ") ``` structure before " - " and after are fundamentally different ``` | eval bracketed = split(mvindex(log, 0), "] [") ``` this part contains brackets ``` | eval keyvalue = mvindex(log, 1) ``` this part contains key-value pairs ``` | rename keyvalue AS _raw ``` for simplicity, we don't care about original _raw here ``` | kv | eval ccnteiT = mvindex(bracketed, 3) ``` this is the part of your interest ``` | rex field=ccnteiT "(?<THEfield>\w+)$" ``` THEfield contains the interested value ```

Using your sample data, you get

THEfield

apiVersion

bracketed

Re: extract fields in json format

vineela — Mon, 09 Jan 2023 09:02:16 GMT

Thanks Splunk champion..That was very clear...I Saw many videos for learning spath command and your solution for my question was crisp and clear...Easily understandable.Very helpful.Thanks a lot again.