Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

calculate average value based on multiple search and static value

crossap
Path Finder
‎05-18-2015 04:52 AM

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!

Tags (2)
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

richgalloway
SplunkTrust
SplunkTrust
‎05-18-2015 05:02 AM

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma
Reply
Solved! Jump to solution

crossap
Path Finder
‎05-18-2015 05:14 AM

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

0 Karma
Reply
Solved! Jump to solution

crossap
Path Finder
‎05-18-2015 07:32 AM

Hi Rich,

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

thanks again

0 Karma
Reply
Solved! Jump to solution

richgalloway
SplunkTrust
SplunkTrust
‎05-18-2015 06:52 AM

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Reply
Solved! Jump to solution
Solution

richgalloway
SplunkTrust
SplunkTrust
‎05-18-2015 05:02 AM

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
---
If this reply helps you, Karma would be appreciated.
0 Karma
Reply
Solved! Jump to solution

crossap
Path Finder
‎05-21-2015 12:38 AM

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

0 Karma
Reply
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...