Splunk Search

How to extract fields in json format?

vineela
Path Finder

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

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

THEfieldapiVersion
bracketed
categoryccnteiTchannelcodecomponententityTypeeventDateTimeeventNameexternalSystemmessagemessageIdentificationproducerproductVersionserviceNameseveritystartsubscriptionIdentificationswiftMessagePartnerBICurluuid
ServiceCalloutEventDataV1
[18:15:21.888
INFO
 
c.c.n.t.e.i.T.ServiceCalloutEventData
akka://MmsAuCluster/user/$b/workMonitorActor/$M+c]
integrational-externalc.c.n.t.e.i.T.ServiceCalloutEventDataAutoNotificationServiceCalloutEventDataweb.clientMNDT2023-01-06T07:15:21.888ZMANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUESTSWIFTPAGSchedule Job start, getNotification requeste1f24a3b8d9111edb3368d1476d87136com.clear2pay.na.mms.au.notification.batch.GetNotificationService2.3.3-0-1-eb5b8caddConsume NotificationINFO167298932188829fbe070057811eca4fa68aa418f5c2aRESTMP01https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications0b8549ff-1f14-4fd5-99c5-b3f2240d7da8

If you need content from any other brackets, you can still use mvindex on bracketed.

View solution in original post

Tags (3)

vineela
Path Finder

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.

vineela_0-1672999993391.png

 

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.

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

0 Karma

vineela
Path Finder

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

| rename _raw as temp, log as _raw
| kv
| rename temp as _raw ``` we are losing log by not renaming _raw back ```
| fields - _raw log ``` so the very long logs are not clogging view ```
| table *

 

0 Karma

vineela
Path Finder

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

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

THEfieldapiVersion
bracketed
categoryccnteiTchannelcodecomponententityTypeeventDateTimeeventNameexternalSystemmessagemessageIdentificationproducerproductVersionserviceNameseveritystartsubscriptionIdentificationswiftMessagePartnerBICurluuid
ServiceCalloutEventDataV1
[18:15:21.888
INFO
 
c.c.n.t.e.i.T.ServiceCalloutEventData
akka://MmsAuCluster/user/$b/workMonitorActor/$M+c]
integrational-externalc.c.n.t.e.i.T.ServiceCalloutEventDataAutoNotificationServiceCalloutEventDataweb.clientMNDT2023-01-06T07:15:21.888ZMANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUESTSWIFTPAGSchedule Job start, getNotification requeste1f24a3b8d9111edb3368d1476d87136com.clear2pay.na.mms.au.notification.batch.GetNotificationService2.3.3-0-1-eb5b8caddConsume NotificationINFO167298932188829fbe070057811eca4fa68aa418f5c2aRESTMP01https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications0b8549ff-1f14-4fd5-99c5-b3f2240d7da8

If you need content from any other brackets, you can still use mvindex on bracketed.

Tags (3)

vineela
Path Finder

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

apiVersioncategorychannelcodecomponententityTypeeventDateTimeeventNameexternalSystemmessagemessageIdentificationproducerproductVersionserviceNameseveritystartsubscriptionIdentificationswiftMessagePartnerBICurluuid
V1integrational-externalAutoNotificationServiceCalloutEventDataweb.clientMNDT2023-01-06T07:15:21.888ZMANDATE_NOTIFICATION_RETRIEVAL.CALLOUT_REQUESTSWIFTPAGSchedule Job start, getNotification requeste1f24a3b8d9111edb3368d1476d87136com.clear2pay.na.mms.au.notification.batch.GetNotificationService2.3.3-0-1-eb5b8caddConsume NotificationINFO167298932188829fbe070057811eca4fa68aa418f5c2aRESTMP01https://sandbox.swift.com/npp-mms/v1/subscriptions/29fbe070057811eca4fa68aa418f5c2a/notifications0b8549ff-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 ```

 

 

Tags (2)
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...