topic Re: Parsing and Displaying a JSON String in Getting Data In

Parsing and Displaying a JSON String

xinlux01rhi — Wed, 13 May 2020 16:53:19 GMT

I have a JSON string as an event in Splunk below:

{"Item1":{"Max":100,"Remaining":80},"Item2":{"Max":409,"Remaining":409},"Item3":{"Max":200,"Remaining":100},"Item4":{"Max":5,"Remaining":5},"Item5":{"Max":2,"Remaining":2}}

Splunk can get fields like "Item1.Max" etc, but when I tried to calculate "Item1.Remaining"/"Item1.Max", it doesn't recognize it as numbers. The convert or tonumber function doesn't work on them.

Also how to convert the string to table like below?

Items Max Remaining Percentage
Item1 100 80 80
Item2 409 409 100
Item3 200 100 50
Item4 5 5 100
Item5 2 2 100

Re: Parsing and Displaying a JSON String

richgalloway — Wed, 13 May 2020 17:23:56 GMT

I also find it challenging to work with fields that name structure. Sometimes it helps to use single quotes instead of double. When that fails, I'll rename the field to something without dots.

Re: Parsing and Displaying a JSON String

to4kawa — Wed, 13 May 2020 18:53:33 GMT

| makeresults 
| eval _raw="{\"Item1\":{\"Max\":100,\"Remaining\":80},\"Item2\":{\"Max\":409,\"Remaining\":409},\"Item3\":{\"Max\":200,\"Remaining\":100},\"Item4\":{\"Max\":5,\"Remaining\":5},\"Item5\":{\"Max\":2,\"Remaining\":2}}" 
| makemv delim="}" _raw 
| stats count by _raw 
| rex mode=sed "s/^(,|{)//g s/$/}/g" 
| rex "Item(?<Items>\d).*(?<json>{.*})" 
| spath input=json 
| eval Percentage=Remaining / Max * 100 
| table Items Max Remaining Percentage 
| sort Items

Re: Parsing and Displaying a JSON String

xinlux01rhi — Wed, 13 May 2020 23:15:41 GMT

Wow, this works out perfectly! Thank you so much!

Re: Parsing and Displaying a JSON String

xinlux01rhi — Wed, 13 May 2020 23:18:19 GMT

Thank you! I find that I have to rename it using spath: | spath output=test path=Item1.Max in order to use test as a number.