topic Re: How to sort by row totals from highest to lowest? in Splunk Search

How to sort by row totals from highest to lowest?

jravida — Thu, 13 Nov 2014 16:38:33 GMT

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

Re: How to sort by row totals from highest to lowest?

aweitzman — Thu, 13 Nov 2014 16:47:21 GMT

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

Re: How to sort by row totals from highest to lowest?

jravida — Thu, 13 Nov 2014 17:04:38 GMT

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.

Re: How to sort by row totals from highest to lowest?

aweitzman — Thu, 13 Nov 2014 17:09:46 GMT

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

Re: How to sort by row totals from highest to lowest?

jravida — Thu, 13 Nov 2014 17:16:48 GMT

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

Re: How to sort by row totals from highest to lowest?

aweitzman — Thu, 13 Nov 2014 17:21:53 GMT

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?

Re: How to sort by row totals from highest to lowest?

jravida — Thu, 13 Nov 2014 17:25:26 GMT

Nope, the column says APPLEBANANAORANGE for every customer

Re: How to sort by row totals from highest to lowest?

aweitzman — Thu, 13 Nov 2014 17:41:59 GMT

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

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

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

Re: How to sort by row totals from highest to lowest?

jravida — Thu, 13 Nov 2014 18:47:35 GMT

That didn't work either...

I managed to get the display using:

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

Re: How to sort by row totals from highest to lowest?

aweitzman — Thu, 13 Nov 2014 19:17:02 GMT

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.