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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...