Splunk Search
×

Are you a member of the Splunk Community?

Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | 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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Take the Survey
Get Updates on the Splunk Community!

Tech Talk Recap | Mastering Threat Hunting

Mastering Threat HuntingDive into the world of threat hunting, exploring the key differences between ...

Observability for AI Applications: Troubleshooting Latency

If you’re working with proprietary company data, you’re probably going to have a locally hosted LLM or many ...

Splunk AI Assistant for SPL vs. ChatGPT: Which One is Better?

In the age of AI, every tool promises to make our lives easier. From summarizing content to writing code, ...