Splunk Search
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
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
Get Updates on the Splunk Community!

[Puzzles] Solve, Learn, Repeat: Dynamic formatting from XML events

This challenge was first posted on Slack #puzzles channelFor a previous puzzle, I needed a set of fixed-length ...

Enter the Agentic Era with Splunk AI Assistant for SPL 1.4

  &#x1f680; Your data just got a serious AI upgrade — are you ready? Say hello to the Agentic Era with the ...

Stronger Security with Federated Search for S3, GCP SQL & Australian Threat ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...