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!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...