Splunk Search

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

tgrogan_dc
New Member

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?

0 Karma
1 Solution

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

nmaiorana
Explorer

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

0 Karma

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

tgrogan_dc
New Member

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...