Splunk Search

sort and calculate the total of "other"

jrodriguezap
Contributor

Hello, I would appreciate a hand with this case, I'm doing the following:
... | chart sum (valueA) AS MB by service | sort-count 5
The result, sort gives 20 rows, I would like the sum total of the other 15 rows after doing the sort. Being thus:

Nombres Suma
fila1 30
fila2 27
fila3 22
fila4 20
fila5 19
Otros 41

Tags (2)
0 Karma

jrodriguezap
Contributor

Thanks, but any idea how could it be? The Top useother has sort does not work?

0 Karma

lukejadamec
Super Champion

I see. I'm sure it can be done, but I'm not sure how. You might want to rewrite the question so that it includes this clarity. There are many others out here smarter than me, but this question was confusing until now.

0 Karma

jrodriguezap
Contributor

Would need to be added before the end of the calculation of "Other".The total sum will guess that adding the "search":
| addcoltotals labelfield=service label=Total

service MB
8009/TCP 172.79
HTTP 85.68
HTTPS 11.28
TCP_SSL 6.62
8080/TCP 1.09
Others 20.00
TOTAL 297.46

0 Karma

lukejadamec
Super Champion

Let me see if I have this right: you want to list the service and MB for the top 5 results, and get a total of MB for Others (everything but the top 5)?

0 Karma

jrodriguezap
Contributor

a similar case to attach to this search?

0 Karma

jrodriguezap
Contributor

someone give me a clue with this please!

0 Karma

jrodriguezap
Contributor

Thank you very much for your support, best I show the entire query:

host=172.24.1.6 | eval service=upper(service) | chart sum(rcvdbyte) AS MB by service | eval MB=round(((MB/1024)/1024),2) | sort 5 -MB  | addcoltotals labelfield=service label=Total

This gives me the following result

**service**     **MB**
8009/TCP    172.79
HTTP        85.68
HTTPS       11.28
TCP_SSL     6.62
8080/TCP    1.09
TOTAL       277.46

Above the row "Total", should be the row "Others" with the sum of all the others that did not appear on the "Top 5"

0 Karma

sdaniels
Splunk Employee
Splunk Employee

enter code herePerhaps you could try using the top command with the useother option.

<search> | stats sum(ValueA) as MB | top 5 useother=true MB

http://docs.splunk.com/Documentation/Splunk/5.0.4/SearchReference/Top

0 Karma

lukejadamec
Super Champion

I tested:

search | top limit=5 useother=true MB | append [same-search | stats sum(MB) AS Total]

The only problem I found was the Total is a column header, not a row label.
The order was correct with OTHER listed after the top list even though it was larger then some other values, and the Total was listed last.
I'm not sure why it is not working for you jrodriquezap.

0 Karma

sdaniels
Splunk Employee
Splunk Employee

You can use '| rename oldfield as newfield' to rename a field. The above search should give you MB as the first column and then count and percentage. You can hide percentage (showperc=false). By definition the top command will give you the top 5 in descending order and then "OTHER". You can use sort -count but i'm assuming this would put OTHER as the highest value.

0 Karma

jrodriguezap
Contributor

It was close, but this disappears the column "Names", and order take out is low to high, I mean the opposite.

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