Splunk Search

How to find percentage with selective sum of field values?

vinothkumark
Explorer

< query > ... | stats count by return_code fetches me the below output.

vinothkumark_0-1674585802845.png


I have to create an alert where the sum of any return_code value other than 100 and 200 should not cross 20% of the overall value.

Example: from the above image, I will add the count of return_codes (other than 100 and 200 ) which will result as 226. now the count of 100 and 200 is 2924. now the  percentage will come around 7.17 %.
How do I achieve this via query?

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

The eventstats should be

| eventstats sum(eval(if(return_code<=200, count, 0))) as OK, sum(eval(if(return_code>200, count, 0))) as KO
| eval pctKO = (KO * 100) / (OK + KO)

however, with your calculations this actually is 3.99%...

| makeresults
| eval x=split("100:254,200:227,404:8,404:3,406:9", ",")
| mvexpand x
| rex field=x "(?<return_code>\d+):(?<count>\d+)"
| table return_code count
| eventstats sum(eval(if(return_code<=200, count, 0))) as OK, sum(eval(if(return_code>200, count, 0))) as KO
| eval pctKO = (KO * 100) / (OK + KO)

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

Just a technical comment on the maths...

When you say that ...should not cross 20% of the overall value.

your calculation of 7.7% indicates that failures is compared only against successes (100+200), i.e 226/2924, however, if you want it to be of 'overall value' then the formula would be

226 / (2924 + 226) = 7.17%

and @richgalloway formula would be

| eval pctKO = (KO * 100) / (OK + KO)

 

0 Karma

vinothkumark
Explorer

yeah,  right. modified it. thanks
226 / (2924 + 226) = 7.17%

0 Karma

richgalloway
SplunkTrust
SplunkTrust

From the current results, use eventstats to compute the subtotals by return_code.  Then you can calculate the percentage.

.. | stats count by return_code
| eventstats sum(eval(return_code<=200)) as OK, sum(eval(return_code>200)) as KO
| eval pctKO = (KO * 100) / OK
---
If this reply helps you, Karma would be appreciated.

vinothkumark
Explorer

Sorry I think I didn't put it in the right way. 
It calculates the return_code like below ( check KO and OK )

vinothkumark_1-1674627569909.png

 

 But I want to calculate the highlighted ones. 
Example: 8+9+3 = 20
254 + 227 = 481
perc = 20*100/(20+481)=3.1% 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

The eventstats should be

| eventstats sum(eval(if(return_code<=200, count, 0))) as OK, sum(eval(if(return_code>200, count, 0))) as KO
| eval pctKO = (KO * 100) / (OK + KO)

however, with your calculations this actually is 3.99%...

| makeresults
| eval x=split("100:254,200:227,404:8,404:3,406:9", ",")
| mvexpand x
| rex field=x "(?<return_code>\d+):(?<count>\d+)"
| table return_code count
| eventstats sum(eval(if(return_code<=200, count, 0))) as OK, sum(eval(if(return_code>200, count, 0))) as KO
| eval pctKO = (KO * 100) / (OK + KO)
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

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

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...