topic Re: Create Pie Chart from JSON data in Splunk Search

Create Pie Chart from JSON data

keiran_harris — Sat, 15 May 2021 10:42:54 GMT

Hey Splunk Gurus!

have been going in circles trying to get a query going to give me a pie chart on what I would have thought is relatively straightforward JSON data.

Heres what the JSON looks like. Id like the pie chart composed of all the pink arrow field values.

can someone help?

thanks so much!

Keiran.

Re: Create Pie Chart from JSON data

kamlesh_vaghela — Sat, 15 May 2021 12:00:56 GMT

@keiran_harris

Ca you please share _raw and expected results in case of multiple event?

Re: Create Pie Chart from JSON data

ITWhisperer — Sat, 15 May 2021 12:34:27 GMT

I have assumed for multiple events you want the sum of the AUD fields. (The first part before the blank lines just generates some sample data.)

| makeresults | eval _raw="{ \"BINNANCE-BALANCES\": { \"BTC\": { \"BTC-AUD\": 3, \"BTC-USDT\": 3 }, \"DOGE\": { \"DOGE-AUD\": 5, \"DOGE-USDT\": 4 }, \"ETC\": { \"ETC-AUD\": 7, \"ETC-USDT\": 5 }, \"ETH\": { \"ETH-AUD\": 9, \"ETH-USDT\": 6 }, \"HBAR\": { \"HBAR-AUD\": 1, \"HBAR-USDT\": 7 } }, \"BINNANCE-TALLY-ADD\": 20, \"kCryptoDictType\": \"BINNANCEbalances\" }|{ \"BINNANCE-BALANCES\": { \"BTC\": { \"BTC-AUD\": 1, \"BTC-USDT\": 3 }, \"DOGE\": { \"DOGE-AUD\": 2, \"DOGE-USDT\": 4 }, \"ETC\": { \"ETC-AUD\": 3, \"ETC-USDT\": 5 }, \"ETH\": { \"ETH-AUD\": 4, \"ETH-USDT\": 6 }, \"HBAR\": { \"HBAR-AUD\": 5, \"HBAR-USDT\": 7 } }, \"BINNANCE-TALLY-ADD\": 20, \"kCryptoDictType\": \"BINNANCEbalances\" }" | eval events=split(_raw,"|") | mvexpand events | fields - _* | rename events as _raw | spath path=BINNANCE-BALANCES | spath input=BINNANCE-BALANCES | fields - _raw _time BINNANCE-BALANCES | fields *-AUD | stats sum(*) as * | transpose 0

Re: Create Pie Chart from JSON data

keiran_harris — Sat, 15 May 2021 21:12:02 GMT

Amazing. Thank you!

So, given i already have the JSON data streaming in, i assume then all i need is the below at the tail end of my search (and sorry, it was stupid of me not to give you guys text data, sorry for the work you had to replicate it manually from the screenshot - thanks for explaining that!).

re your assumption of “sum” i believe all i need actually is “last” . As in, my code (that spits out the JSON) is hitting an API that is like a balance. So im not interested in summing balanced over the search period, but rather just plotting the latest value that arrived to give me the latest balance. So in that case do i just replace “sum” in your second last line with “last”?

finally, do i need the 4th last line (fields….) or is that a hangover of you having to work with my screenshot

| spath path=BINNANCE-BALANCES
| spath input=BINNANCE-BALANCES
| fields - _raw _time BINNANCE-BALANCES
| fields *-AUD
| stats sum(*) as *
| transpose 0

thanks again. So impressed at turnaround time, esp on a weekend!

keiran.

Re: Create Pie Chart from JSON data

ITWhisperer — Sat, 15 May 2021 21:54:28 GMT

Yes, last instead of sum for your use case.

The transpose flips rows to columns so they appear as different series in the pie chart.

Re: Create Pie Chart from JSON data

keiran_harris — Sun, 16 May 2021 00:15:34 GMT

ok tried it, its darn close, but its duplicating that data set, at several levels of the JSON indenting. Any final tweaks? I can re-write my python code if needed to spit out the JSON with different names if that helps. It seems I might have to do that for the TALLY one (that also ends in -AUD) Thanks again!

Re: Create Pie Chart from JSON data

keiran_harris — Sun, 16 May 2021 05:04:19 GMT

got there with fiddling! thanks for setting me on the right path.