Getting Data In

Parsing and Displaying a JSON String

xinlux01rhi
Explorer

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

Tags (3)
0 Karma
1 Solution

to4kawa
Ultra Champion
| 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

View solution in original post

to4kawa
Ultra Champion
| 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
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

xinlux01rhi
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...