calculate average value based on multiple search and static value

crossap — Mon, 18 May 2015 11:52:44 GMT

Hi,

I am still working on my SANS dashboard and am looking to create a value based upon multiple searches and static values

I will explain the current setup as best I can

The SANS 3 score will be derived from adding together the results from these searches and static value (all contained within a seperate dashboard)

SANS 3.1

| dbquery "database" "SELECT * FROM new_compliancelist"| stats count(eval(FAILED<1)) as success count as total | eval value = success/total*100 (lets say its 79 %)

SANS 3.2

| stats count as value | eval value = 64

SANS 3.3

| stats count as value | eval value = 50

SANS 3 value should be 79+64+50/3=64.3 avg score

My query is how can I link the searches together and also include the static values that are not searches but just a manual eval value=?

thanks!

Re: calculate average value based on multiple search and static value

richgalloway — Mon, 18 May 2015 12:02:41 GMT

Something like this, perhaps?

| dbquery "database" "SELECT * FROM new_compliancelist"| stats count(eval(FAILED<1)) as success count as total | eval SANS31 = success/total*100 | eval SANS32=64 | eval SANS33=50 | eval SANS3=(SANS31+SANS32+SANS33)/3

Re: calculate average value based on multiple search and static value

crossap — Mon, 18 May 2015 12:14:24 GMT

Hi Rich,

thanks for your reply

Its very nearly there -the only issue being the Eval = for sans 32 & 33. I would like this to be pulled through (dynamic) so that they only need amended in one location

I am not sure if this is even possible

here is my current complete code

  <single>
    <title>Standard secure configuration</title>
     <search>
       <query>| dbquery "database" "SELECT * FROM new_compliancelist"| stats count(eval(FAILED&lt;1)) as success count as total | eval value = success/total*100 | rangemap field=value elevated=51-84 low=85-100 severe=0-50 default=none</query>
      <earliest>-15m</earliest>
      <latest>now</latest>
    </search>
    <option name="field">value</option>
    <option name="classField">range</option>
    <option name="linkView">search</option>
    <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.scale">linear</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.enabled">false</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.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.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">right</option>
    <option name="drilldown">none</option>
  </single>
  <html> <a href="https://splunkserver/en-US/app/Sans_Pmc/sans_311">Additional Information</a> </html>
</panel>
<panel>
  <title>SANS 3.2</title>
  <single>
    <title>Automated patching</title>
    <search>
 | stats count as value | eval value = 64 | rangemap field=value elevated=51-84 low=85-100 severe=0-50 default=none
      <earliest>-15m</earliest>
      <latest>now</latest>
    </search>
    <option name="field">value</option>
    <option name="classField">range</option>
    <option name="linkView">search</option>
    <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.scale">linear</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.enabled">false</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.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.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">right</option>
    <option name="drilldown">none</option>
  </single>
  <html> <a href="https://splunkserver/en-GB/app/Sans_Pmc/sans_32">Additional Information</a> </html>
</panel>


<panel>
  <title>SANS 3.3</title>
  <single>
    <title>Limit Administrative priviledges</title>
     <search>
 | stats count as value | eval value = 80 | rangemap field=value elevated=51-84 low=85-100 severe=0-50 default=none
      <earliest>-15m</earliest>
      <latest>now</latest>
    </search>
    <option name="field">value</option>
    <option name="classField">range</option>
    <option name="linkView">search</option>
    <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.scale">linear</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.enabled">false</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.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.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">right</option>
    <option name="drilldown">none</option>
  </single>
  <html> <a href="https://splunkserver/en-GB/app/Sans_Pmc/sans_33">Additional Information</a> </html>
</panel>

Is it possible to pull through the | stats count as value | eval value = 80 in the above to the new search? this would allow the non dynamic results to only be updated in 1 location.

Hope the above makes sense - sorry it's hard to show/explain without pictures

Re: calculate average value based on multiple search and static value

richgalloway — Mon, 18 May 2015 13:52:06 GMT

I believe you can do that using Advanced XML, but not with Simple XML.

Re: calculate average value based on multiple search and static value

crossap — Mon, 18 May 2015 14:32:35 GMT

Hi Rich,

Ahh ok - hopefully someone who has experience of html will be able to assist if they see this post.

thanks again

Re: calculate average value based on multiple search and static value

crossap — Thu, 21 May 2015 07:38:07 GMT

Hi Rich,

you're the closest answer - I have slightly changed it and now have another issue which I shall log separate

thanks for your help

topic calculate average value based on multiple search and static value in Splunk Search

calculate average value based on multiple search and static value

Re: calculate average value based on multiple search and static value

Re: calculate average value based on multiple search and static value

Re: calculate average value based on multiple search and static value

Re: calculate average value based on multiple search and static value

Re: calculate average value based on multiple search and static value