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!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...