Splunk Search

## How can I define a total amount of a Pie Chart?

New Member

Hi!
I need to create a pie chart where the full pie is 1000000 and the "usage" is a count number.
It should look like this:

I have Json data like this:
{
Name: ApplicationX
NumberOfUsers: 70
Version: 2013
}

And I'm using the following Splunk query:
MY QUERY | eval Total=1000000 | chart count(NumberOfUsers) by Total

Tags (4)
1 Solution
SplunkTrust

@gcescatto, based on the description seems like your Number Of User upper limit is set to 1000000 which is 100% for pie chart.

If you just want to display two values i.e. `Used` vs `Total Max`, you should better use on of Gauge Visualizations like `Radial Gauge` in this case. (https://docs.splunk.com/Documentation/Splunk/latest/Viz/CreateGauges#Radial_gauge)

Pie chart is more suitable when you have multiple slices. Not for Used (25%) vs Total(100%). Even if you had two slices they would be Used (25%) vs Unused (75%), so that the two slices would make up the pie (100%). Just to answer your question and make the scenario for pie more relevant I have actually created a run anywhere example (mocking sample jSON provided) to calculate Usage by Application so that we get multiple slices.

Following is the run anywhere search (The pipes till `spath` mocks data, you would need to plug in your base search to return Name, NumberOfUsers from your jSON data)

``````|  makeresults
|  eval _raw="{\"Name\": \"ApplicationX\",\"NumberOfUsers\": 70000,\"Version\": 2013}"
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 20000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2013}"
]
|  spath
|  append
[|  makeresults
|  eval Name="Un-Used", Version=2013]
|  eval TotalMax=1000000
|  stats sum(NumberOfUsers) as NumberOfUsers last(TotalMax) as TotalMax  by Name
|  eventstats sum(NumberOfUsers) as TotalNumberOfUsersAccounted
|  eval NumberOfUsers=if(Name=="Un-Used",TotalMax-TotalNumberOfUsersAccounted,NumberOfUsers)
|  eval perc=round((NumberOfUsers/TotalMax)*100,1)
|  eval Name=Name."(value=".NumberOfUsers.", perc=".perc."%)"
|  table Name NumberOfUsers
``````

Step 1: Using append "Un-Used" data needs to be added.
Step 2: Using eval `TotalMax` needs to be set at 1000000
Step 3: using stats total NumberOfUsers per Application is calculated (this is assuming each application may have multiple events in your data. If not you can use `table` as well. However, `stats` will work in both cases ๐ )
Step 4: Use eventstats to calculate running total of all NumberOfUsers across all applications and attach `TotalNumberOfUsersAccounted` to each event.
Step 5: Use eval to calculate percentage as `perc`
Step 6. Use eval to change Name to also contain `value` i.e. `NumberOfUsers` and percentage calculated as `perc` by each Application.
Step 7: Create `table` of only require Name Value pair i.e. `Name`, `NumberOfUsers`
Step 8: Set the visualization as Pie Chart.

Please find the run anywhere dashboard on the same example.

``````<dashboard>
<label>Pie Chart used vs unused</label>
<row>
<panel>
<title>Number of Users Used vs Unused Pie Chart - Total(max)=1000000</title>
<chart>
<search>
<query>|  makeresults
|  eval _raw="{\"Name\": \"ApplicationX\",\"NumberOfUsers\": 70000,\"Version\": 2013}"
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 20000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2013}"
]
|  spath
|  append
[|  makeresults
|  eval Name="Un-Used", Version=2013]
|  eval TotalMax=1000000
|  stats sum(NumberOfUsers) as NumberOfUsers last(TotalMax) as TotalMax  by Name
|  eventstats sum(NumberOfUsers) as TotalNumberOfUsersAccounted
|  eval NumberOfUsers=if(Name=="Un-Used",TotalMax-TotalNumberOfUsersAccounted,NumberOfUsers)
|  eval perc=round((NumberOfUsers/TotalMax)*100,1)
|  eval Name=Name."(value=".NumberOfUsers.", perc=".perc."%)"
|  table Name NumberOfUsers</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">visible</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.abbreviation">none</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.abbreviation">none</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.abbreviation">none</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">pie</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">none</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.mode">standard</option>
<option name="charting.legend.placement">right</option>
<option name="charting.lineWidth">2</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
</chart>
</panel>
</row>
</dashboard>
``````
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
SplunkTrust

The whole pie represents Total (100 %) so it won't be possible show your count and total as slice in pie chart. Options for you would be
1) Use post process search panels, may be a single value or html panel to show Total before/after the pie chart one. Where pie chart will show 'Used' and 'Remaining '.
2) Show 'Used' and 'Remaining' in pie chart and add the Total value in the column labels.

SplunkTrust

@gcescatto, based on the description seems like your Number Of User upper limit is set to 1000000 which is 100% for pie chart.

If you just want to display two values i.e. `Used` vs `Total Max`, you should better use on of Gauge Visualizations like `Radial Gauge` in this case. (https://docs.splunk.com/Documentation/Splunk/latest/Viz/CreateGauges#Radial_gauge)

Pie chart is more suitable when you have multiple slices. Not for Used (25%) vs Total(100%). Even if you had two slices they would be Used (25%) vs Unused (75%), so that the two slices would make up the pie (100%). Just to answer your question and make the scenario for pie more relevant I have actually created a run anywhere example (mocking sample jSON provided) to calculate Usage by Application so that we get multiple slices.

Following is the run anywhere search (The pipes till `spath` mocks data, you would need to plug in your base search to return Name, NumberOfUsers from your jSON data)

``````|  makeresults
|  eval _raw="{\"Name\": \"ApplicationX\",\"NumberOfUsers\": 70000,\"Version\": 2013}"
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 20000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2013}"
]
|  spath
|  append
[|  makeresults
|  eval Name="Un-Used", Version=2013]
|  eval TotalMax=1000000
|  stats sum(NumberOfUsers) as NumberOfUsers last(TotalMax) as TotalMax  by Name
|  eventstats sum(NumberOfUsers) as TotalNumberOfUsersAccounted
|  eval NumberOfUsers=if(Name=="Un-Used",TotalMax-TotalNumberOfUsersAccounted,NumberOfUsers)
|  eval perc=round((NumberOfUsers/TotalMax)*100,1)
|  eval Name=Name."(value=".NumberOfUsers.", perc=".perc."%)"
|  table Name NumberOfUsers
``````

Step 1: Using append "Un-Used" data needs to be added.
Step 2: Using eval `TotalMax` needs to be set at 1000000
Step 3: using stats total NumberOfUsers per Application is calculated (this is assuming each application may have multiple events in your data. If not you can use `table` as well. However, `stats` will work in both cases ๐ )
Step 4: Use eventstats to calculate running total of all NumberOfUsers across all applications and attach `TotalNumberOfUsersAccounted` to each event.
Step 5: Use eval to calculate percentage as `perc`
Step 6. Use eval to change Name to also contain `value` i.e. `NumberOfUsers` and percentage calculated as `perc` by each Application.
Step 7: Create `table` of only require Name Value pair i.e. `Name`, `NumberOfUsers`
Step 8: Set the visualization as Pie Chart.

Please find the run anywhere dashboard on the same example.

``````<dashboard>
<label>Pie Chart used vs unused</label>
<row>
<panel>
<title>Number of Users Used vs Unused Pie Chart - Total(max)=1000000</title>
<chart>
<search>
<query>|  makeresults
|  eval _raw="{\"Name\": \"ApplicationX\",\"NumberOfUsers\": 70000,\"Version\": 2013}"
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 20000,\"Version\": 2012}"
]
|  append
[ makeresults
|  eval _raw="{\"Name\": \"ApplicationY\",\"NumberOfUsers\": 30000,\"Version\": 2013}"
]
|  spath
|  append
[|  makeresults
|  eval Name="Un-Used", Version=2013]
|  eval TotalMax=1000000
|  stats sum(NumberOfUsers) as NumberOfUsers last(TotalMax) as TotalMax  by Name
|  eventstats sum(NumberOfUsers) as TotalNumberOfUsersAccounted
|  eval NumberOfUsers=if(Name=="Un-Used",TotalMax-TotalNumberOfUsersAccounted,NumberOfUsers)
|  eval perc=round((NumberOfUsers/TotalMax)*100,1)
|  eval Name=Name."(value=".NumberOfUsers.", perc=".perc."%)"
|  table Name NumberOfUsers</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
<sampleRatio>1</sampleRatio>
</search>
<option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
<option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">visible</option>
<option name="charting.axisTitleY2.visibility">visible</option>
<option name="charting.axisX.abbreviation">none</option>
<option name="charting.axisX.scale">linear</option>
<option name="charting.axisY.abbreviation">none</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.axisY2.abbreviation">none</option>
<option name="charting.axisY2.enabled">0</option>
<option name="charting.axisY2.scale">inherit</option>
<option name="charting.chart">pie</option>
<option name="charting.chart.bubbleMaximumSize">50</option>
<option name="charting.chart.bubbleMinimumSize">10</option>
<option name="charting.chart.bubbleSizeBy">area</option>
<option name="charting.chart.nullValueMode">gaps</option>
<option name="charting.chart.showDataLabels">none</option>
<option name="charting.chart.sliceCollapsingThreshold">0.01</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.chart.style">shiny</option>
<option name="charting.drilldown">none</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
<option name="charting.legend.mode">standard</option>
<option name="charting.legend.placement">right</option>
<option name="charting.lineWidth">2</option>
<option name="trellis.enabled">0</option>
<option name="trellis.scales.shared">1</option>
<option name="trellis.size">medium</option>
</chart>
</panel>
</row>
</dashboard>
``````
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
New Member

WOOOOOOOW!
Thank you so much!
I can definitely work with this!
Learned a lot (:

SplunkTrust

Anytime @gcescatto, we all learn everyday. Do think about Splunk Answers when you run into issues or want to help others ๐

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
State of Splunk Careers