Splunk Search

How to sort by row totals from highest to lowest?

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)
1 Solution
Communicator

That didn't work either...

I managed to get the display using:

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

Communicator

That didn't work either...

I managed to get the display using:

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

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.

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

Motivator

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

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

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?

Communicator

Nope, the column says APPLEBANANAORANGE for every customer

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?)