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!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...