Parsing and Displaying a JSON String

[xinlux01rhi]

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

[to4kawa · Accepted solution]

| 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

[xinlux01rhi]

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

[richgalloway]

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.

[xinlux01rhi]

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.