Need trellis of hourly pie-charts by http_status

[NK]

Splunk sourcetype=access_combined.   What would the splunk query look like to get an hourly trellis of piecharts by http_status?

[tscroggins · Accepted solution]

Hi @NK,

@gcusello provides a hint about aggregations and split-by fields used in trellis mode. Generally, any output field in the by-clause of an aggregation command like chart, stats, or timechart will be available as a split-by field in trellis, and other fields will be treated as aggregations.

You can use timechart as a helper to fill in empty hour and status values:

index=main sourcetype=access_combined
| timechart fixedrange=true limit=0 span=1h useother=false count by status
| untable _time status count
| rename _time as hour
| stats sum(count) as count by hour status
| fieldformat hour=strftime(hour, "%F %T")

When fixedrange=false, timechart will limit its output to the lower and upper _time bounds of the search results.

When fixedrange=true, timechart will add empty buckets using the lower and upper _time bounds of your search time range, e.g., earliest=-24h@h latest=@h.

When no results are found for an hour of the day, an empty pie chart will be displayed, and missing or zero status values relative to the search results will be aggregated with other status values under the minimum size threshold using the label "other":

[gcusello]

Hi @NK ,

you cannot use timechart but eval and stats, then you can configure something like this:

<dashboard version="1.1" theme="light">
  <label>Test trellis</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>
              index=_internal 
              | eval hour=strftime(_time,"%H")  
              | stats count BY hour sourcetype
          </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">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisTitleY2.visibility">collapsed</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">none</option>
        <option name="charting.lineWidth">2</option>
        <option name="trellis.enabled">1</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
        <option name="trellis.splitBy">hour</option>
      </chart>
    </panel>
  </row>
</dashboard>

Ciao.

Giuseppe