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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...