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

@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

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

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...