How to add addcoltotals functionality to show average instead of the total?

[tgrogan_dc]

I have a table of technologies (WindowsOS, LinuxOS, OracleDB, JBossWeb, etc) and associated security compliance ratings based on some metric evaluations. An example table of results looks like this:

Technology  | Compliance
WindowsOS   |    92
LinuxOS     |    89
OracleDB    |    72

What I would like is to add another row for an Overall Compliance rating, which would be an average of the Compliance values.

addcoltotals works perfectly from a format perspective but I need an average rather than a total.
Ultimately I would like to get a result like this:

Technology  | Compliance
WindowsOS   |    92
LinuxOS     |    89
OracleDB    |    72
Overall     |    84

I am using the results to drive a radial gauge visualization and I would like to Trellis it by Technology so it will display compliance ratings for each technology component as well as an overall rating.

I can obviously create a separate panel for the overall rating and a trellis panel to visualize each technology but I would prefer to have all in one.

The number of rows will vary from 1 to however many technologies are in use for a system. eval doesn't support stats functions or this would be very straightforward. Is there a way to get a count of the rows returned that can be used in an eval command or some other method of doing this that I am missing?

[nmaiorana]

It appears as though users would like this to be a function like addcoltotals. Is Splunk planning on adding this feature (and possibly other stats functions) for totals?

Thanks

[niketn · Accepted solution]

@tgrogan_dc, please try adding the following to your current search, the appendpipe command will calculate average using stats and another final stats will be required to create Trellis. The streamstats to add serial number is added to have Radial Gauge in same sequence when broken out by Trellis layout.

<yourCurrentSearch>
| appendpipe 
    [| stats avg(Compliance) as Compliance 
    | eval Compliance=round(Compliance,1) 
    | eval Technology=if(isnull(Technology),"Overall",Technology)]
| streamstats count as sno
| eval Technology= sno.". ".Technology
| stats last(Compliance) as Compliance by Technology

Please try the following run anywhere example:

<dashboard>
  <label>appendpipe avg radial gauge</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults 
| fields - _time 
| eval data="WindowsOS|92;LinuxOS|89;OracleDB|72" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="|" 
| eval Technology=mvindex(data,0),Compliance=mvindex(data,1) 
| fields - data 
| table Technology Compliance 
| appendpipe 
    [| stats avg(Compliance) as Compliance 
    | eval Compliance=round(Compliance,1) 
    | eval Technology=if(isnull(Technology),"Overall",Technology)]
| streamstats count as sno
| eval Technology= sno.". ".Technology
| stats last(Compliance) as Compliance by Technology</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">radialGauge</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">all</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">Technology</option>
      </chart>
    </panel>
  </row>
</dashboard>

[tgrogan_dc]

@niketnilay: OMG....I can't believe I didn't think about that! I'm actually using append to add results from multiple indexes so I should have gone that route. I got tunnel vision going down this rabbit hole.

Very much appreciate the assistance.