Splunk Search

How to find percentage with selective sum of field values?

vinothkumark
Path Finder

< 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
Path Finder

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
Path Finder

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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...