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
That didn't work either...
I managed to get the display using:
... |addtotals | sort -Total | fields - Total
That didn't work either...
I managed to get the display using:
... |addtotals | sort -Total | fields - Total
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.
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
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.
Can you please provide a sample table of what this might look like?
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
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?
Nope, the column says APPLEBANANAORANGE for every customer
Weird. What if you put single quotes around the field names?
| eval allfruit='APPLE'+'BANANA'+'ORANGE'
(Also, what version of Splunk are you using?)