Splunk Search

Create Pie Chart from JSON data

keiran_harris
Path Finder

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. 

20210515_20-37-52_Search  Splunk 8.0.3(2).jpg 

can someone help? 

thanks so much! 

Keiran. 

Labels (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

keiran_harris
Path Finder

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

keiran_harris
Path Finder

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! 

keiran_harris_1-1621123880489.jpeg

 

0 Karma

keiran_harris
Path Finder

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

keiran_harris_0-1621141447285.jpeg

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@keiran_harris 

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...