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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

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

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...