Splunk Search

Top to a sum

jrodriguezap
Contributor

Hello!
I try to make the sum of a field, but then need to get the percentage occupied by each of the first 4, and% occupying all others, ie get something like this:

**Service**    **SumMB**        **percent**
  HHTP            90               25.71
  HTTPS           85               22.45
  DNS             81               21.09
  SMTP            70               16.90
  Otros           23               13.85

I tried something like this, but that would be missing something but, as you could?

SEARCH | stats sum(MB) AS SumMB by service | top SumMB otherstr="Others"

Thank you for your attention.
:)

Tags (3)
0 Karma
1 Solution

HiroshiSatoh
Champion

"Others" is displayed if I assume it "useother=true". However, I think that this isn't the result that you expect.

SEARCH | stats sum(MB) AS SumMB by service | top SumMB useother=true otherstr="Others"

I think that it is necessary to calculate percent by oneself.

For example
SEARCH | stats sum(MB) AS SumMB by service |sort - SumMB|eval id=1|accum id|eval Service=if(id<=4,service,"OTHER")|stats sum(SumMB) as SumMB by Service|addcoltotals labelfield=Service label=ALL|sort - SumMB|eval Total=IF(Service="ALL",SumMB,0)|accum Total|WHERE Service!="ALL"|eval percent=SumMB/Total*100|fields - Total

comment

|sort - SumMB|eval id=1|accum id|eval Service=if(id<=4,service,"OTHER")|->extract TOP4 and "OTHER"
|addcoltotals labelfield=Service label=ALL|sort - SumMB|eval Total=IF(Service="ALL",SumMB,0)|accum Total|WHERE Service!="ALL"|->Calculation of the total

When there is little number, you may demand total by a subsearch.

View solution in original post

HiroshiSatoh
Champion

"Others" is displayed if I assume it "useother=true". However, I think that this isn't the result that you expect.

SEARCH | stats sum(MB) AS SumMB by service | top SumMB useother=true otherstr="Others"

I think that it is necessary to calculate percent by oneself.

For example
SEARCH | stats sum(MB) AS SumMB by service |sort - SumMB|eval id=1|accum id|eval Service=if(id<=4,service,"OTHER")|stats sum(SumMB) as SumMB by Service|addcoltotals labelfield=Service label=ALL|sort - SumMB|eval Total=IF(Service="ALL",SumMB,0)|accum Total|WHERE Service!="ALL"|eval percent=SumMB/Total*100|fields - Total

comment

|sort - SumMB|eval id=1|accum id|eval Service=if(id<=4,service,"OTHER")|->extract TOP4 and "OTHER"
|addcoltotals labelfield=Service label=ALL|sort - SumMB|eval Total=IF(Service="ALL",SumMB,0)|accum Total|WHERE Service!="ALL"|->Calculation of the total

When there is little number, you may demand total by a subsearch.

jrodriguezap
Contributor

Your response and your logic are excellent!
Thank you very much HiroshiSatoh!

0 Karma

jrodriguezap
Contributor

Sorry for the confusion, but the result I'm trying to get is the one show as a table. I hope you understand.

0 Karma

lukejadamec
Super Champion

In your question you ask for the sum of the first 4 with a percent, and then a percent of the others, but in your example you show the sum and percent for all 5. Your goal is not clear.
The question: "I tried something like this, but that would be missing something but, as you could?" does not compute. Can you review this question, please.

0 Karma
Get Updates on the Splunk Community!

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, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...