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

gcescatto — Mon, 06 Nov 2017 17:24:24 GMT

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

Could someone please help me?

Re: How can I define a total amount of a Pie Chart?

niketn — Mon, 06 Nov 2017 18:54:40 GMT

@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>

Re: How can I define a total amount of a Pie Chart?

somesoni2 — Mon, 06 Nov 2017 19:09:44 GMT

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.

Re: How can I define a total amount of a Pie Chart?

gcescatto — Tue, 07 Nov 2017 12:29:48 GMT

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

Re: How can I define a total amount of a Pie Chart?

niketn — Tue, 07 Nov 2017 14:14:12 GMT

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

topic Re: How can I define a total amount of a Pie Chart? in Splunk Search

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

Re: How can I define a total amount of a Pie Chart?

Re: How can I define a total amount of a Pie Chart?

Re: How can I define a total amount of a Pie Chart?

Re: How can I define a total amount of a Pie Chart?