Splunk Search

Extract JSON objects

vishaltaneja070
Motivator

How can i extract this:
"properties": {"nextLink": null,
"columns": [
{"name": "Cost", "type": "Number"},
{"name": "Date", "type": "Number"},
{"name": "Charge", "type": "String"},
{"name": "Publisher", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Service", "type": "Array"},
{"name": "Standard", "type": "String"},
"rows": [
[2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", "[app:"new","type":"band"]", "HHH"],
[2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", "[app:"old","type":"land"]", "YYY"] ]

No of columns can be increased.

@ITWhisperer Can you help?

Labels (3)
0 Karma

vishaltaneja070
Motivator

@ITWhisperer  Sorry my bad, wrong json. can you please try now. I have tested this json on jsonviewer.

{"properties": {"nextLink": null, 
"columns": [
{"name": "Cost", "type": "Number"}, 
{"name": "Usage", "type": "Number"}, 
{"name": "Charge", "type": "String"},
{"name": "Type", "type": "String"}, 
{"name": "Res", "type": "String"}, 
{"name": "ResGF", "type": "String"}, 
{"name": "Name", "type": "String"}, 
{"name": "Reso", "type": "String"}, 
{"name": "Tag", "type": "Array"}, 
{"name": "Wallet", "type": "String"}], 
"rows": [[0.01564, 20200708, "usage", "gcp", "unknown", "hhhh", "newwww", "zyyyyy", ["\"app\":\"hhh\"", "\"created\":\"hhh\""], "USD"],[0.064, 20200708, "usage", "gcp", "new", "zyy", "hhh", "zyyyyy", ["\"app\":\"work\"", "\"hum\":\"zhhh\""], "USD"]]}}

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You posted about this a month ago - did this solution not work?

https://community.splunk.com/t5/Splunk-Search/Extract-JSON-objects/m-p/562583#M196043 

0 Karma

vishaltaneja070
Motivator

@ITWhisperer 

Its looks same but its different,
now it has json object inside rows also:

"[app:"new","type":"band"]",

which is causing issue.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The issue is note to do with it trying to be a JSON object (app should be in double quotes to be JSON), the problem is that there are embedded quotes in the string. Can you get the format changed to escape the embedded quotes with a backslash?

0 Karma

vishaltaneja070
Motivator

@ITWhisperer 
Sorry app is also in double quotes only
"properties": {"nextLink": null,
"columns": [
{"name": "Cost", "type": "Number"},
{"name": "Date", "type": "Number"},
{"name": "Charge", "type": "String"},
{"name": "Publisher", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Service", "type": "Array"},
{"name": "Standard", "type": "String"},
"rows": [
[2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", ["app":"new","type":"band"], "HHH"],
[2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", ["app":"old","type":"land"], "YYY"] ]

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is not valid JSON syntax, the columns collection should be closed with a right brace and the embedded collection should be converted to an object e.g.

{"properties": {"nextLink": null,
"columns": [
{"name": "Cost", "type": "Number"},
{"name": "Date", "type": "Number"},
{"name": "Charge", "type": "String"},
{"name": "Publisher", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Resource", "type": "String"},
{"name": "Service", "type": "Array"},
{"name": "Standard", "type": "String"}
],
"rows": [
[2.06, 20210807, "usage", "uuuu", "hhh", "gd", "bandwidth", {"app":"new","type":"band"}, "HHH"],
[2.206, 20210807, "usage", "uuuhhh", "ggg", "gd", "bandwidth", {"app":"old","type":"land"}, "YYY"] ]
}

Please can you check your real data?

0 Karma

vishaltaneja070
Motivator

@ITWhisperer  Sorry my bad, wrong json. can you please try now. I have tested this json on jsonviewer.

 

{"properties": {"nextLink": null, 
"columns": [
{"name": "Cost", "type": "Number"}, 
{"name": "Usage", "type": "Number"}, 
{"name": "Charge", "type": "String"},
{"name": "Type", "type": "String"}, 
{"name": "Res", "type": "String"}, 
{"name": "ResGF", "type": "String"}, 
{"name": "Name", "type": "String"}, 
{"name": "Reso", "type": "String"}, 
{"name": "Tag", "type": "Array"}, 
{"name": "Wallet", "type": "String"}], 
"rows": [[0.01564, 20200708, "usage", "gcp", "unknown", "hhhh", "newwww", "zyyyyy", ["\"app\":\"hhh\"", "\"created\":\"hhh\""], "USD"],[0.064, 20200708, "usage", "gcp", "new", "zyy", "hhh", "zyyyyy", ["\"app\":\"work\"", "\"hum\":\"zhhh\""], "USD"]]}}

 

 

0 Karma

vishaltaneja070
Motivator

@ITWhisperer Just tagged you if you have seen my response

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

So the issue is with the embedded array and knowing which "column" it is in, and knowing how many elements there are in each instance of the array. spath can extract it e.g. rows.{}{}{} but they all end up in the same mv field and you can't tell how many belong to the first row and how many belong to the second row etc. You would also have to inspect the type fields for the column to work out which field requires an array of elements. I haven't figured out a way to do this.

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...