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!
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
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<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
Hi Rich,
Ahh ok - hopefully someone who has experience of html will be able to assist if they see this post.
thanks again
I believe you can do that using Advanced XML, but not with Simple XML.
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
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