Splunk Search

How to retrieve data form keys value?

prashantsagar73
Explorer

i have a table who contain multiple keys and value one of them keys{"body"} value are below:

"body": "{\n \"Type\" : \"Notification\",\n \"MessageId\" : \"f33b9756-bc6b-5efc-8111-cca792b8d4f3\",\n \"TopicArn\" : \"arn:aws:sns:eu-central-1:108770896200:PL-PRD-notification-media\",\n \"Message\" : \"{\\\"licenseValidFrom\\\":\\\"2022-11-18T07:56:18.760+01:00\\\",\\\"licenseValidUntil\\\": \\\"3022-03-21T07:56:18.760+01:00\\\",\\\"hasCopyright\\\":\\\"False\\\",\\\"resolutionInPx\\\": \\\"685x1664\\\",\\\"resolutionKey\\\":\\\"ORIGINAL\\\",\\\"checksum\\\":\\\"35a63f43ec3088c9cf01b6c5473f1436\\\", \\\"description\\\": \\\"Jewelry Full\\\", \\\"brand\\\": \\\"\\\", \\\"category\\\": \\\"\\\", \\\"mediaType\\\": \\\"AdditionalImage\\\", \\\"status\\\": \\\"Media.Active.490.Finished\\\", \\\"gtin\\\": \\\"9009656409602\\\", \\\"channel\\\": \\\"gkkDigitalDataManagement\\\", \\\"mediaId\\\": \\\"06\\\", \\\"contentType\\\": \\\"image/jpeg\\\"}\",\n \"Timestamp\" : \"2022-11-18T06:56:19.980Z\",\n \"SignatureVersion\" : \"1\",\n \"Signature\" : \"AySfxHK6Y3ZSA7BsgR7sFHva82snBuenk74ZMJ5HzewU4ozOg8PDOnjeBAY0FLbFxomWOEVIzNWp9yW8Ti9lWWNpdzeMd4MYUhN/a0tLwce1Dk0xdAlsM9DByiJHUTWj1QkvUsaJChMaDfZOyFwZNhvHBbtC9W/Y9AtcZnS9ahz8bQBvxIZv/Xb7tK/g0pvOJ2Nx633TN1UStYshQef8g1cV+q4Ey0fMRr9l/K00POuBUCcGZRRXTiGaqVOTWk08ARFsW5a9Iz28kaBz4PDFNdCALgnwdZ65m6k2HL8fYW5O7gvxEqAOLnYcPsX8XLiV20tSd2NBgoytq5f3IxAbsw==\",\n \"MessageAttributes\" : {\n \"channel\" : {\"Type\":\"String\",\"Value\":\"gkkDigitalDataManagement\"},\n \"mediaStatus\" : {\"Type\":\"String\",\"Value\":\"Media.Active.490.Finished\"},\n \"mediaType\" : {\"Type\":\"String\",\"Value\":\"AdditionalImage\"}\n }\n}",

want to retrieve [gtin: 9009656409602] in a separate table

Labels (5)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

You need to remove spurious characters first.

| eval "cxlBusinessData.data.body" = replace('c', "\\\n ", "")
| spath input=cxlBusinessData.data.body
| spath input=Message
| table gtin

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Since you have escaped embedded JSON, you could try extracting in stages.

| spath
| spath input=body
| spath input=Message
0 Karma

prashantsagar73
Explorer

not working.

my query is : table cxlBusinessData.data.body| spath | spath input=body | spath input=Message

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Sounds like your raw event is not pure JSON - please can you share an anonymised version of your raw events in a code block </>

prashantsagar73
Explorer

the previous result was belong from this data body

0 Karma

prashantsagar73
Explorer



0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Which field is this data extracted to - for example if it was called body

| spath input=body
| spath input=cxlBusinessData.data.body
| spath input=Message
0 Karma

prashantsagar73
Explorer

it gives u the same output like before one if u call body.

my code :

| table cxlBusinessData.data.body

this one gives me the body part(output) only that i attached with the question, but from that body i want data{ gtin} in a separate table. so i can use it further .

0 Karma

yuanliu
SplunkTrust
SplunkTrust

You need to remove spurious characters first.

| eval "cxlBusinessData.data.body" = replace('c', "\\\n ", "")
| spath input=cxlBusinessData.data.body
| spath input=Message
| table gtin
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...