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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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