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)
0 Karma
1 Solution

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

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

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

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

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

Motivator

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

0 Karma

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

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

Communicator

Nope, the column says APPLEBANANAORANGE for every customer

0 Karma

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