How to generate a Pie chart?

[mmouse88]

Hi

I have following search and the line chart displays fine. When changing to a pie chart, the display doesn't look the same as line chart where it displays by the user. Line chart's legend shows users but changing to the pie chart in Splunk visualization, it displays by _time. Here are two type of searches which perform the same thing:

index=name  | timechart sum(eval(quantity/12)) span=1h by user

Or this accelerated data model

|tstats summariesonly=true sum(Job.quantity) as sum_qty FROM datamodel= WHERE (Job.quantity > 0 AND (Job.feature =)) by Job.user, _time span=5min | stats sum(sum_qty) as qty by Job.user, _time | timechart sum(eval(qty)) span=5m by Job.user

[niketn]

@mmouse88, if your main search is supposed to generate a timechart through a transpose command, then you can use Post Processing in Splunk to send the results from timechart to another search and perform stats to get the results for pie chart.

Refer to the following run anywhere dashboard example where first query (base search - baseStatsQuery) prepares stats with _time and component. The second search (post processing from base search baseStatsQuery) uses the results to prepare a time chart for top 25 component (limit=0 in timechart can plot all components). The timechart command is also transforming command which is used for recursive post-processing ( i.e. recurTimechartQuery) to get sum of components for pie chart using transpose and addtotals command.

Following is the run anywhere Simple XML Dashboard code:
PS:
1. span=1h for the example. Change as per need.
2. Instead of fillnullcommand in the timechart, Chart configuration has been used: <option name="charting.chart.nullValueMode">gaps</option>
3. Limit 25 has been applied in timechart and transpose commands. Refer to documentation to set limit to 0 to have all series. However, for Pie slices and even for timechart data points there should be some limit applied.
4. Collapsing threshold 0.0001 has been applied to Pie Chart to have as many slices as possible before collapsing as OTHERS field i.e. <option name="charting.chart.sliceCollapsingThreshold">0.00001</option>

<form>
  <label>Pie chart from Timechart query</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Timechart - Post Process Search</title>
      <chart>
        <search base="baseStatsQuery" id="recurTimechartQuery">
          <query>| timechart span=1h sum(count) by component useother=f limit=25</query>
        </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">area</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">zero</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="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
    <panel>
      <title>Pie Chart - Post Process Search (Recursive)</title>
      <chart>
        <search base="recurTimechartQuery">
          <query> addtotals col=t row=f labelfield="_time" label="Total" 
| search _time="Total" 
| transpose 25 header_field="_time" column_name="component" 
| search component!="_*"</query>
        </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.00001</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="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Table - Base Search</title>
      <table>
        <search id="baseStatsQuery">
          <query>index=_internal sourcetype=splunkd log_level!="INFO" component=* 
| bin _time span=1h 
| stats count by _time component</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

[mmouse88]

Thx niketnilay, your transpose hint also helped me in recalling how to use it. But unfortunately, i can accept both yours and adonio

[niketn]

@mmouse88 you can definitely up vote multiple answers/comments 🙂

I just wanted to document the proper use of Post Processing (in fact recursive) and your question seemed the right opportunity ! Although I am sowing data in three different forms I have fetched the required data from index only once in the base search.

[s2_splunk]

It uses _time, because you explicitly asked for that by using timechart. Try rewriting your search to use stats or chart. (Also: docs)

[adonio · Accepted solution]

hello there,

timechart command creates 2 dimensions (X,Y) time and the stats you are working with (and split by if you split)
the piechart is one dimensional (X only) and therefore takes only 1 field and splits it . it is useful for other queries, not so much when you want to split by time.
try and use chart instead

hope it helps

[mmouse88]

Thx Adonio, You helped in recalling my memory in using chart. Here's the command I used to generate a pie chart:

index=name | bucket span=1h _time | stats sum(eval(quantity/12)) as total by _time, user | eval total=round(total) | chart first(total) as total over _time by user limit=10 | stats max(*) as * | transpose 2 | rename column as "user" | rename "row 1" as "Peak_Use" | sort - Peak_Use