Splunk Search

How to sort by row totals from highest to lowest?

jravida
Communicator

Hi Folks,
I'm having problems sorting a chart. I want to take the overall totals in one row and sort by that. Here's a simple version:

index=customerchoice snackChoice=fruit | chart count(eval(fruitName=apple)) as APPLE, count(eval(fruitName=banana)) as BANANA, count(eval(fruitName=orange)) as ORANGE by customerName

I can make this into a chart and it looks good, but it is only pulling the customer names alphabetically. I'd like to see who ate the most fruit total on top, but the top command just zeros out all my fields, weirdly (so I know I'm using it wrong). SO if Bill ate 7 apples, 6 bananas and 12 oranges, he would be above Jill who only ate one of each. But if Zack ate 40 oranges, he would end up on top. Hope that makes sense

Tags (2)
0 Karma
1 Solution

jravida
Communicator

That didn't work either...

I managed to get the display using:

... |addtotals | sort -Total | fields - Total

View solution in original post

0 Karma

jravida
Communicator

That didn't work either...

I managed to get the display using:

... |addtotals | sort -Total | fields - Total

0 Karma

aweitzman
Motivator

Well, I'm glad you found something that works. I have never had it behave the way you described above, and as you might expect, "it works on my machine" :-), so I'm not sure where my suggestions went wrong. But the important thing is that you've solved your problem.

0 Karma

aweitzman
Motivator

Just create a sum, sort by that, and then remove the sum field so it doesn't end up in the graph:

...your search... | eval allfruit=APPLE+BANANA+ORANGE | sort -allfruit | fields - allfruit
0 Karma

jravida
Communicator

I tried this but it is still sorting alphabetically. I don't think the eval statement works because there is only one occurrence of fruitchoice per meal. So in a single log of a complete meal, it would only say apple, banana or orange for the field, not a value. Sorry if I was unclear.

0 Karma

aweitzman
Motivator

Can you please provide a sample table of what this might look like?

0 Karma

jravida
Communicator

I'm just gonna type it out because it's on a standalone, sorry it looks ugly, this website crams it all together

CustomerName APPLE ORANGE BANANA
Angela 1 0 5
Bill 7 5 15
Mark 0 0 1
Steve 24 8 0

So I would want Steve, then Bill, Angela, Mark, for the total of fruit they took
Hope this looks OK

0 Karma

aweitzman
Motivator

That's pretty much what I thought it would look like. If you just add

| eval allfruit=APPLE+BANANA+ORANGE

do you get another column with the sum?

0 Karma

jravida
Communicator

Nope, the column says APPLEBANANAORANGE for every customer

0 Karma

aweitzman
Motivator

Weird. What if you put single quotes around the field names?

| eval allfruit='APPLE'+'BANANA'+'ORANGE'

(Also, what version of Splunk are you using?)

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