topic Re: Troubling json extraction in Splunk Search

Troubling json extraction

Cuyose — Tue, 29 Apr 2014 17:38:47 GMT

I can't for the life of me figure this out. There seem to be examples all over and none of them address this.

I have a json element that looks like this.(I reduced the comma delimited elements to 2 for clarity, but there are dozens in the same format.
{"status":"success","data":{"11-01-00251":"88","11-01-00247":"378"}}

Splunk is displaying the event in structured json just fine in the event list. What I want to do is be able to report on this data. This is inventory. So the first data element is the sku, and the integer is the quantity. Basically how do I search to be able to do a

|chart inventory by sku?

Re: Troubling json extraction

Cuyose — Tue, 29 Apr 2014 19:34:16 GMT

Maybe even a regex that can extract this would be useful, Splunk's field extraction can't seem to figure it out.

Re: Troubling json extraction

aweitzman — Wed, 30 Apr 2014 15:47:12 GMT

You're trying to get Splunk to interpret field names as field values, which isn't easy. You might be well served to rewrite the output (either at the source, or possibly via a transform) like so:

{"status":"success","data":[{"sku":"11-01-00251","inventory":"88"},{"sku":"11-01-00247","inventory":"378"}]}

Then you can work with it through spath using something like example 3 on the spath documentation page.

Re: Troubling json extraction

Cuyose — Wed, 30 Apr 2014 16:16:35 GMT

Thats what Im resorting to, I have a python script to do it and it's all done except for the little part where its adding a comma on the last data object invalidating the json.

for sku in data:
print '{"sku":"'+ sku + '","count":"' + str(data[sku]) + '"},'

Re: Troubling json extraction

somesoni2 — Mon, 28 Sep 2020 16:30:26 GMT

This seems to work for me.

|stats count | eval _raw="{\"status\":\"success\",\"data\":{\"11-01-00251\":\"88\",\"11-01-00247\":\"378\"}}" | rex max_match=0 "\"(?[^\"]*)\":\"(?\d+)"

Re: Troubling json extraction

aweitzman — Wed, 30 Apr 2014 16:31:15 GMT

You might want to consider a method that does the string manipulation for a single unit of "data", and then using python's "join" command to put the results together the way you want. (I don't really know python, but that seems to be how it ought to work based on what I found on the internet.)
Since you're adding it in as text, you might want to avoid naming the second field "count" since that's also a Splunk command.